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

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

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 insert33 as34 begin35 declare @InputUser nvarchar(50),@CreateTime datetime36 select @InputUser=[InputUser], @CreateTime=[CreateTime] from inserted37 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 update54 as55 begin56 declare @UpdateUser nvarchar(50),57 @ModifyTime datetime ,58 @fieldCount int,---字段个数59 @BeforeModifyValue nvarchar(50),60 @AfterModifyValue nvarchar(50),61 @currentFieldID int ,----当前刚刚插入到FieldModifyLog里的id62 @fieldName nvarchar(50),----列名63 @InputUser nvarchar(50),64 @CreateTime datetime65 ----获得列名66 select @fieldCount=count(*) from syscolumns where id=object_id('''+@tName+''')67

68 ---循环每列69 while @fieldCount>070 begin71

72 ---1.首先判断是否有更新,73 if substring( columns_updated() , (@fieldCount-1)/8+1 , 1 ) & power( 2, (@fieldCount-1)%8 ) > 074 begin75 ---2.若有更新，获得列名76 set @fieldName=(select col_name(object_id('''+@tName+'''),@fieldCount))77

78 ---3.判断被修改的列是不是''ModifyUser'',''ModifyTime''，若不是则向日志表中插入相关记录79 if(@fieldName not in(''ModifyUser'',''ModifyTime''))80 begin81

82 --3.1.1 判断关于deleted的临时表是否存在，若存在删除83 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N''tempdb..#backDel'') and type=''U'')84 begin85 drop table #backDel86 end87

88

89 --3.1.2填充deleted临时表90 select * into #backDel from deleted91

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 output96

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 begin101 drop table #backInsert102 end103

104

105 --3.2.2填充临时表106 select * into #backInsert from inserted107

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 output112

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-1123

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