创建触发器，将数据库中表的修改记录进日志表

93 阅读 0 评论 62 点赞

我是靠谱客的博主鳗鱼茉莉，这篇文章主要介绍创建触发器，将数据库中表的修改记录进日志表，现在分享给大家，希望可以做个参考。

要求：

假定有一个数据库，有10张表，每张表都有inputuser和createtime,modifyuser,modifytime 4个字段用来记录数据库记录的变动
请为该数据库建立一个日志表，可以记录每个字段的修改情况，日志信息包括创建、修改的用户以及时间，字段名名等信息
并为10张表创建触发器，自动记录日志
触发器的创建要求采用一段通用的代码自动为所有的表创建触发器

备注：

所有以'Table_Log_%'的表为10个对象表

表 FieldModifyLog 为修改的字段信息包括字段名，修改前的值，修改后的值

表 LogRecords 包含字段被修改的表名 inputuser createtime modifyuser modifytime 被修改的列（即为表FieldModifyLog）

复制代码

1 declare @count int ,---表的个数
  2 @i int ,---控制循环的次数
  3 @tName nvarchar(50),----表名    
  4 @sql nvarchar(max),---用于插入的触发器语句
  5 @sqlUpdate nvarchar(max),---用于更新的触发器语句
  6 @sqlDropTriggerUpdate nvarchar(max), ---删除已存在触发器  for  update
  7 @sqlDropTrigger nvarchar(max)----删除已存在触发器  for insert
  8 
  9 set @i=0
 10 ---得到表的个数
 11 select @count= count(*) from dbo.sysobjects where type='u' and name like 'Table_Log_%'
 12 
 13 
 14 ----循环为每个表创建触发器
 15 while @i<@count
 16 begin
 17     set @i=@i+1 
 18     ---获得当前的表名
 19     select @tName=[name] from (select row_number()over(order by [name]) as hanghao, name from dbo.sysobjects where type='u' and name like 'Table_Log_%') as tt where hanghao=@i
 20 
 21 
 22     ---判断insert触发器是否存在，若存在删掉
 23     if   exists(select * from dbo.sysobjects where Name ='Log_'+ @tName +'_Insert' AND type = 'TR')
 24     begin
 25     set @sqlDropTrigger='drop trigger Log_'+@tName+'_Insert'
 26     exec(@sqldropTrigger)
 27     end
 28 
 29 
 30     ---拼接创建触发器for  insert 语句
 31     set @sql='create trigger Log_'+@tName+'_Insert on '+ @tName+' 
 32     for insert 
 33     as 
 34     begin
 35     declare @InputUser nvarchar(50),@CreateTime datetime
 36     select  @InputUser=[InputUser], @CreateTime=[CreateTime] from inserted
 37     insert into LogRecords(TableName,InputUser,CreateTime) values('''+@tName+''',@InputUser,@CreateTime)
 38     end'  
 39 
 40 
 41     ---判断update触发器是否存在，若存在删掉
 42     if  exists(select * from dbo.sysobjects where Name ='Log_'+@tName+'_Update' AND type = 'TR')
 43     begin
 44     set @sqlDropTriggerUpdate='drop trigger Log_'+@tName+'_Update'
 45     exec(@sqlDropTriggerUpdate)
 46     end
 47 
 48 
 49     --拼接创建触发器for  update 语句
 50     set @sqlUpdate='
 51     ----创建触发器
 52     create trigger Log_'+@tName+'_Update on '+ @tName +'
 53     for update
 54     as
 55     begin
 56         declare @UpdateUser nvarchar(50),
 57         @ModifyTime datetime ,
 58         @fieldCount int,---字段个数
 59         @BeforeModifyValue nvarchar(50),
 60         @AfterModifyValue nvarchar(50),
 61         @currentFieldID int ,----当前刚刚插入到FieldModifyLog里的id
 62         @fieldName nvarchar(50),----列名
 63         @InputUser nvarchar(50),
 64         @CreateTime datetime
 65         ----获得列名
 66         select @fieldCount=count(*) from syscolumns where id=object_id('''+@tName+''')
 67 
 68         ---循环每列 
 69         while @fieldCount>0
 70         begin
 71 
 72             ---1.首先判断是否有更新,
 73             if substring( columns_updated() , (@fieldCount-1)/8+1 , 1 ) & power( 2, (@fieldCount-1)%8 ) > 0
 74             begin
 75                 ---2.若有更新，获得列名
 76                 set @fieldName=(select col_name(object_id('''+@tName+'''),@fieldCount))
 77 
 78                 ---3.判断被修改的列是不是''ModifyUser'',''ModifyTime''，若不是则向日志表中插入相关记录
 79                 if(@fieldName not in(''ModifyUser'',''ModifyTime''))
 80                 begin
 81 
 82                     --3.1.1 判断关于deleted的临时表是否存在，若存在删除
 83                     if exists (select * from tempdb.dbo.sysobjects where id = object_id(N''tempdb..#backDel'') and type=''U'')
 84                     begin
 85                       drop table #backDel
 86                     end
 87 
 88 
 89                     --3.1.2填充deleted临时表
 90                     select * into #backDel from  deleted 
 91 
 92                     --3.1.3得到更新前的值
 93                     declare @sqlBeforeModify nvarchar(max),@outPutBefore nvarchar(50)
 94                     set @sqlBeforeModify=N''select @BeforeModifyValue=''+@fieldName+N''   from #backDel''
 95                     exec sp_executesql @sqlBeforeModify,N''@BeforeModifyValue nvarchar(50) output'',@outPutBefore output
 96 
 97 
 98                     --3.2.1 判断关于inserted临时表是否存在,若存在，则删除
 99                     if exists (select * from tempdb.dbo.sysobjects where id = object_id(N''tempdb..#backInsert'') and type=''U'')
100                     begin
101                          drop table #backInsert
102                     end
103                     
104 
105                     --3.2.2填充临时表
106                     select * into #backInsert from  inserted
107 
108                     --3.2.3得到更新后的值
109                     declare @sqlAfterModify nvarchar(max),@outPutAfter nvarchar(50)
110                     set @sqlAfterModify=''select @AfterModifyValue=''+@fieldName+'' from #backInsert''
111                     exec sp_executesql @sqlAfterModify,N''@AfterModifyValue nvarchar(50) output'',@outPutAfter output
112 
113                     select @UpdateUser=ModifyUser,@ModifyTime=ModifyTime,@InputUser=InputUser,@CreateTime=CreateTime from '+@tName+'
114 
115                     --3.3向日志表中插入
116                     insert into FieldModifyLog values(@fieldName,@outPutBefore,@outPutAfter)
117                     set @currentFieldID=IDENT_CURRENT(''FieldModifyLog'')
118                     insert into LogRecords(TableName,ModifyUser,ModifyTime,ModifyField,InputUser,CreateTime) values('''+@tName+''',@UpdateUser,@ModifyTime,@currentFieldID,@InputUser,@CreateTime)
119                 end------end注释3.
120 
121             end----end注释1.
122             set @fieldCount=@fieldCount-1
123 
124         end---end循环列的while循环
125 
126     end ---end触发器的创建 
127     '
128 
129     exec(@sqlUpdate)
130 
131     ----执行创建语句
132 
133 
134     exec(@sql)
135 end
136 
137 
138

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
  1 declare @count int ,---表的个数
  2 @i int ,---控制循环的次数
  3 @tName nvarchar(50),----表名    
  4 @sql nvarchar(max),---用于插入的触发器语句
  5 @sqlUpdate nvarchar(max),---用于更新的触发器语句
  6 @sqlDropTriggerUpdate nvarchar(max), ---删除已存在触发器  for  update
  7 @sqlDropTrigger nvarchar(max)----删除已存在触发器  for insert
  8 
  9 set @i=0
 10 ---得到表的个数
 11 select @count= count(*) from dbo.sysobjects where type='u' and name like 'Table_Log_%'
 12 
 13 
 14 ----循环为每个表创建触发器
 15 while @i<@count
 16 begin
 17     set @i=@i+1 
 18     ---获得当前的表名
 19     select @tName=[name] from (select row_number()over(order by [name]) as hanghao, name from dbo.sysobjects where type='u' and name like 'Table_Log_%') as tt where hanghao=@i
 20 
 21 
 22     ---判断insert触发器是否存在，若存在删掉
 23     if   exists(select * from dbo.sysobjects where Name ='Log_'+ @tName +'_Insert' AND type = 'TR')
 24     begin
 25     set @sqlDropTrigger='drop trigger Log_'+@tName+'_Insert'
 26     exec(@sqldropTrigger)
 27     end
 28 
 29 
 30     ---拼接创建触发器for  insert 语句
 31     set @sql='create trigger Log_'+@tName+'_Insert on '+ @tName+' 
 32     for insert 
 33     as 
 34     begin
 35     declare @InputUser nvarchar(50),@CreateTime datetime
 36     select  @InputUser=[InputUser], @CreateTime=[CreateTime] from inserted
 37     insert into LogRecords(TableName,InputUser,CreateTime) values('''+@tName+''',@InputUser,@CreateTime)
 38     end'  
 39 
 40 
 41     ---判断update触发器是否存在，若存在删掉
 42     if  exists(select * from dbo.sysobjects where Name ='Log_'+@tName+'_Update' AND type = 'TR')
 43     begin
 44     set @sqlDropTriggerUpdate='drop trigger Log_'+@tName+'_Update'
 45     exec(@sqlDropTriggerUpdate)
 46     end
 47 
 48 
 49     --拼接创建触发器for  update 语句
 50     set @sqlUpdate='
 51     ----创建触发器
 52     create trigger Log_'+@tName+'_Update on '+ @tName +'
 53     for update
 54     as
 55     begin
 56         declare @UpdateUser nvarchar(50),
 57         @ModifyTime datetime ,
 58         @fieldCount int,---字段个数
 59         @BeforeModifyValue nvarchar(50),
 60         @AfterModifyValue nvarchar(50),
 61         @currentFieldID int ,----当前刚刚插入到FieldModifyLog里的id
 62         @fieldName nvarchar(50),----列名
 63         @InputUser nvarchar(50),
 64         @CreateTime datetime
 65         ----获得列名
 66         select @fieldCount=count(*) from syscolumns where id=object_id('''+@tName+''')
 67 
 68         ---循环每列 
 69         while @fieldCount>0
 70         begin
 71 
 72             ---1.首先判断是否有更新,
 73             if substring( columns_updated() , (@fieldCount-1)/8+1 , 1 ) & power( 2, (@fieldCount-1)%8 ) > 0
 74             begin
 75                 ---2.若有更新，获得列名
 76                 set @fieldName=(select col_name(object_id('''+@tName+'''),@fieldCount))
 77 
 78                 ---3.判断被修改的列是不是''ModifyUser'',''ModifyTime''，若不是则向日志表中插入相关记录
 79                 if(@fieldName not in(''ModifyUser'',''ModifyTime''))
 80                 begin
 81 
 82                     --3.1.1 判断关于deleted的临时表是否存在，若存在删除
 83                     if exists (select * from tempdb.dbo.sysobjects where id = object_id(N''tempdb..#backDel'') and type=''U'')
 84                     begin
 85                       drop table #backDel
 86                     end
 87 
 88 
 89                     --3.1.2填充deleted临时表
 90                     select * into #backDel from  deleted 
 91 
 92                     --3.1.3得到更新前的值
 93                     declare @sqlBeforeModify nvarchar(max),@outPutBefore nvarchar(50)
 94                     set @sqlBeforeModify=N''select @BeforeModifyValue=''+@fieldName+N''   from #backDel''
 95                     exec sp_executesql @sqlBeforeModify,N''@BeforeModifyValue nvarchar(50) output'',@outPutBefore output
 96 
 97 
 98                     --3.2.1 判断关于inserted临时表是否存在,若存在，则删除
 99                     if exists (select * from tempdb.dbo.sysobjects where id = object_id(N''tempdb..#backInsert'') and type=''U'')
100                     begin
101                          drop table #backInsert
102                     end
103                     
104 
105                     --3.2.2填充临时表
106                     select * into #backInsert from  inserted
107 
108                     --3.2.3得到更新后的值
109                     declare @sqlAfterModify nvarchar(max),@outPutAfter nvarchar(50)
110                     set @sqlAfterModify=''select @AfterModifyValue=''+@fieldName+'' from #backInsert''
111                     exec sp_executesql @sqlAfterModify,N''@AfterModifyValue nvarchar(50) output'',@outPutAfter output
112 
113                     select @UpdateUser=ModifyUser,@ModifyTime=ModifyTime,@InputUser=InputUser,@CreateTime=CreateTime from '+@tName+'
114 
115                     --3.3向日志表中插入
116                     insert into FieldModifyLog values(@fieldName,@outPutBefore,@outPutAfter)
117                     set @currentFieldID=IDENT_CURRENT(''FieldModifyLog'')
118                     insert into LogRecords(TableName,ModifyUser,ModifyTime,ModifyField,InputUser,CreateTime) values('''+@tName+''',@UpdateUser,@ModifyTime,@currentFieldID,@InputUser,@CreateTime)
119                 end------end注释3.
120 
121             end----end注释1.
122             set @fieldCount=@fieldCount-1
123 
124         end---end循环列的while循环
125 
126     end ---end触发器的创建 
127     '
128 
129     exec(@sqlUpdate)
130 
131     ----执行创建语句
132 
133 
134     exec(@sql)
135 end
136 
137 
138