在SqlServer2008R2中，在一张表上加上insert、update、delete触发器（带游标）

在日常工作中，在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