SQLSERVER触发器inserted和deleted表

2016-12-24 08:34:46来源:作者:人点击

SQLSERVER触发器inserted和deleted表。在触发器语句中用两个特殊的表一个是deleted表和inserted。它们是通过触发器操作自动创建驻留在内存中的临时表。

Deleted表用于存储 DELETE和 UPDATE语句所影响的行的复本。在执行DELETE或 UPDATE语句时,行从触发器表中删除,并传输到 deleted表中。Deleted表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

如果我们想要获取我们刚插入或删除数据库中的内容,就可以从这两张表中获取

下面附上我写的例子,就是当向人工录入数据表中插入数据时,获取插入的数据值,并操作其他表格

USE [DB_GreenPower]
GO /****** Object: Trigger [dbo].[deleteData] Script Date: 2016/12/17 22:53:06 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE TRIGGER [dbo].[deleteData]
ON [dbo].[TB_DataBase_ManEntry_Status]
AFTER INSERT
AS
BEGIN
--获取插入时间的第一天对应的时间
declare @time datetime,@RecTime datetime
select @RecTime=RecTime from inserted
set @time=CONVERT(datetime,DATENAME(year, @RecTime)+'-'+DATENAME(MONTH, @RecTime)+'-1')
--添加完人工录入数据以后,删除之前创建的人工录入数据
--删除电网的数据
delete from TB_DataHandle_PN_OneHour where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_PN_OneDay where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_PN_OneMouth where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
--删除电厂的数据
delete from TB_DataHandle_PP_OneHour where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_PP_OneDay where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_PP_OneMouth where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
--删除输配环节的数据
delete from TB_DataHandle_TADL_OneHour where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_TADL_OneDay where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_TADL_OneMouth where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
--删除电网汇总的数据
delete from TB_DataHandle_TotalPN_OneDay where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
delete from TB_DataHandle_TotalPN_OneMouth where RecTime>@time and RecTime<=DATEADD(MONTH,1,@time)
--判断年汇总数据是否删除
declare @month int
set @month=DATENAME(YEAR,@RecTime)
if @month=12
begin
delete from TB_DataHandle_TotalPN_OneYear where RecTime>=DATEADD(MONTH,-12,@time) and RecTime<@time
end
END GO

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台