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

直接上代码吧：

 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

 

posted @ 2012-09-05 14:49  shungdawei  阅读(533)  评论(0)  编辑  收藏  举报