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

要求：

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

备注：

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

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

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

declare @count int ,---表的个数
@i int ,---控制循环的次数
@tName nvarchar(50),----表名    
@sql nvarchar(max),---用于插入的触发器语句
@sqlUpdate nvarchar(max),---用于更新的触发器语句
@sqlDropTriggerUpdate nvarchar(max), ---删除已存在触发器  for  update
@sqlDropTrigger nvarchar(max)----删除已存在触发器  for insert

set @i=0
---得到表的个数
select @count= count(*) from dbo.sysobjects where type='u' and name like 'Table_Log_%'


----循环为每个表创建触发器
while @i<@count
begin
    set @i=@i+1 
    ---获得当前的表名
    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


    ---判断insert触发器是否存在，若存在删掉
    if   exists(select * from dbo.sysobjects where Name ='Log_'+ @tName +'_Insert' AND type = 'TR')
    begin
    set @sqlDropTrigger='drop trigger Log_'+@tName+'_Insert'
    exec(@sqldropTrigger)
    end


    ---拼接创建触发器for  insert 语句
    set @sql='create trigger Log_'+@tName+'_Insert on '+ @tName+' 
    for insert 
    as 
    begin
    declare @InputUser nvarchar(50),@CreateTime datetime
    select  @InputUser=[InputUser], @CreateTime=[CreateTime] from inserted
    insert into LogRecords(TableName,InputUser,CreateTime) values('''+@tName+''',@InputUser,@CreateTime)
    end'  


    ---判断update触发器是否存在，若存在删掉
    if  exists(select * from dbo.sysobjects where Name ='Log_'+@tName+'_Update' AND type = 'TR')
    begin
    set @sqlDropTriggerUpdate='drop trigger Log_'+@tName+'_Update'
    exec(@sqlDropTriggerUpdate)
    end


    --拼接创建触发器for  update 语句
    set @sqlUpdate='
    ----创建触发器
    create trigger Log_'+@tName+'_Update on '+ @tName +'
    for update
    as
    begin
        declare @UpdateUser nvarchar(50),
        @ModifyTime datetime ,
        @fieldCount int,---字段个数
        @BeforeModifyValue nvarchar(50),
        @AfterModifyValue nvarchar(50),
        @currentFieldID int ,----当前刚刚插入到FieldModifyLog里的id
        @fieldName nvarchar(50),----列名
        @InputUser nvarchar(50),
        @CreateTime datetime
        ----获得列名
        select @fieldCount=count(*) from syscolumns where id=object_id('''+@tName+''')

        ---循环每列 
        while @fieldCount>0
        begin

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

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

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


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

                    --3.1.3得到更新前的值
                    declare @sqlBeforeModify nvarchar(max),@outPutBefore nvarchar(50)
                    set @sqlBeforeModify=N''select @BeforeModifyValue=''+@fieldName+N''   from #backDel''
                    exec sp_executesql @sqlBeforeModify,N''@BeforeModifyValue nvarchar(50) output'',@outPutBefore output


                    --3.2.1 判断关于inserted临时表是否存在,若存在，则删除
                    if exists (select * from tempdb.dbo.sysobjects where id = object_id(N''tempdb..#backInsert'') and type=''U'')
                    begin
                         drop table #backInsert
                    end
                    

                    --3.2.2填充临时表
                    select * into #backInsert from  inserted

                    --3.2.3得到更新后的值
                    declare @sqlAfterModify nvarchar(max),@outPutAfter nvarchar(50)
                    set @sqlAfterModify=''select @AfterModifyValue=''+@fieldName+'' from #backInsert''
                    exec sp_executesql @sqlAfterModify,N''@AfterModifyValue nvarchar(50) output'',@outPutAfter output

                    select @UpdateUser=ModifyUser,@ModifyTime=ModifyTime,@InputUser=InputUser,@CreateTime=CreateTime from '+@tName+'

                    --3.3向日志表中插入
                    insert into FieldModifyLog values(@fieldName,@outPutBefore,@outPutAfter)
                    set @currentFieldID=IDENT_CURRENT(''FieldModifyLog'')
                    insert into LogRecords(TableName,ModifyUser,ModifyTime,ModifyField,InputUser,CreateTime) values('''+@tName+''',@UpdateUser,@ModifyTime,@currentFieldID,@InputUser,@CreateTime)
                end------end注释3.

            end----end注释1.
            set @fieldCount=@fieldCount-1

        end---end循环列的while循环

    end ---end触发器的创建 
    '

    exec(@sqlUpdate)

    ----执行创建语句


    exec(@sql)
end