判断 SQLServer 触发器类型，支持多行

最新推荐文章于 2023-12-25 10:49:07 发布

转载最新推荐文章于 2023-12-25 10:49:07 发布 · 240 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/shungdawei/archive/2012/09/05/2671943.html

文章标签：

#数据库

本文介绍了一个SQL触发器的具体实现案例，该触发器用于监控[dbo].[OpTypeLog]表上的INSERT、UPDATE和DELETE操作，并记录这些操作到一个名为[log]的表中。通过定义不同的游标和条件判断，确保每次数据库变动都能被准确记录。

直接上代码吧：

 1 ALTER TRIGGER [dbo].[tgr_forOpType]
 2 ON [dbo].[OpTypeLog]
 3 FOR  INSERT, UPDATE, DELETE 
 4 AS
 5 begin
 6 declare @id int
 7 declare @tableName varchar(50)
 8 set @tableName = 'TableName'
 9 declare @opType varchar(50)
10 --insert--------------------------------------------------------
11     if exists(select * from inserted) and not exists (select * from deleted)
12     begin
13         set @opType = 'insert'
14         declare cur cursor
15         for select * from inserted
16         open cur
17         fetch next from cur
18         --into @id
19         while @@fetch_status = 0
20         begin
21             insert into [log](OpType,TableName) values (@opType,@tableName)
22             fetch cur --into @id
23         end
24         close cur
25         deallocate cur
26     end
27 --delete--------------------------------------------------------
28 if exists(select * from deleted) and not exists (select * from inserted)
29     begin
30         set @opType = 'delete'
31         declare cur cursor
32         for select * from deleted
33         open cur
34         fetch next from cur
35         --into @id
36         while @@fetch_status = 0
37         begin
38             insert into [log](OpType,TableName) values (@opType,@tableName)
39             fetch cur --into @id
40         end
41         close cur
42         deallocate cur
43     end
44 --update--------------------------------------------------------
45 if exists(select * from deleted) and exists (select * from inserted)
46     begin
47         set @opType = 'update'
48         declare cur cursor
49         for select * from deleted
50         open cur
51         fetch next from cur
52         --into @id
53         while @@fetch_status = 0
54         begin
55             insert into [log](OpType,TableName) values (@opType,@tableName)
56             fetch cur --into @id
57         end
58         close cur
59         deallocate cur
60     end
61 end