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

107 阅读 0 评论 71 点赞

我是靠谱客的博主坚强蜻蜓，这篇文章主要介绍在SqlServer2008R2中，在一张表上加上insert、update、delete触发器（带游标），现在分享给大家，希望可以做个参考。

在日常工作中，在SqlServer2008R2中，需要向一张表上加上触发器，监控插入、更新、删除。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--一个触发器内三种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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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