在日常工作中,在SqlServer2008R2中,需要向一张表上加上触发器,监控插入、更新、删除。
--一个触发器内三种INSERT,UPDATE,DELETE状态
IF exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
--INSERT
end
IF exists(select 1 from inserted) and exists(select 1 from deleted)
begin
--UPDATE
end
IF exists(select 1 from deleted) and not exists(select 1 from inserted)
begin
--DELETE
end
--插入操作(Insert):Inserted表有数据,Deleted表无数据
--删除操作(Delete):Inserted表无数据,Deleted表有数据
--更新操作(Update):Inserted表有数据(新数据),Deleted表有数据(旧数据)
下面是我这写的语句,供网友借鉴、参考:
CREATE trigger [dbo].[deal_Trace_globe_Data] on [dbo].[DirectPriceZoneAndBunding]
for insert,update,delete as
begin
--插入或更新
IF exists(select 1 from inserted)
begin
DECLARE @ID nvarchar(50)
DECLARE @CustCode nvarchar(8)
DECLARE @Version int
DECLARE @OperateTime datetime
DECLARE c1 CURSOR for
SELECT [ID],[CustCode],[Version],[OperateTime] from inserted
OPEN c1
FETCH NEXT FROM c1 into @ID,@CustCode,@Version,@OperateTime
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @Count int
DECLARE @No_Count int
--插入
if not exists(select 1 from deleted)
begin
SELECT @Count = count(1) FROM trace_globe.dbo.DirectPriceZoneAndBunding WHERE ID = (select ID from inserted)
SELECT @No_Count = COUNT(1) FROM inserted
IF @Count <=0 and @No_Count>0
begin
insert into trace_globe.dbo.DirectPriceZoneAndBunding([ID],[CustCode],[Version],[OperateTime])
select [ID],[CustCode],[Version],[OperateTime] from inserted where ID=@ID
end
end
else
--更新
begin
SELECT @Count = count(1) FROM trace_globe.dbo.DirectPriceZoneAndBunding WHERE ID = (select ID from deleted)
SELECT @No_Count = COUNT(1) FROM deleted
IF @Count >0 and @No_Count>0
begin
update trace_globe.dbo.DirectPriceZoneAndBunding
set [ID]=@ID,[CustCode]=@CustCode,[Version]=@Version,[OperateTime]=@OperateTime
where [ID]=@ID
end
end
FETCH NEXT FROM c1 into @ID,@CustCode,@Version,@OperateTime
END
CLOSE c1
DEALLOCATE c1
end
--删除
IF exists(select 1 from deleted) and not exists(select 1 from inserted)
begin
DECLARE @deleteID nvarchar(50)
DECLARE c2 CURSOR for
SELECT [ID] from deleted
OPEN c2
FETCH NEXT FROM c2 into @deleteID
WHILE @@FETCH_STATUS=0
BEGIN
delete from trace_globe.dbo.DirectPriceZoneAndBunding where ID=@deleteID
FETCH NEXT FROM c2 into @deleteID
END
CLOSE c2
DEALLOCATE c2
end
end