用触发器记录数据库表记录更改日志

 

Create TRIGGER [dbo].[triINF_OrganLog]
   ON  dbo.INF_Organ
   AFTER DELETE,UPDATE
AS
BEGIN
 SET NOCOUNT ON; 
 
 --**************设置增加触发器的表
 declare @table varchar(100)
 set @table='INF_Organ' --******************************************需要设置表名
 
 --**************如果日志表不存在则生成日志表
 if object_id('Logs_'+@table) is null 
 begin
  --生成日志表
  exec ('select * into Logs_'+@table+' from '+@table+' where 1<>1')
  --增加日志字段
  exec ('alter table Logs_'+@table+' add LogId int IDENTITY (1,1) NOT NULL primary key') 
  exec ('alter table Logs_'+@table+' add LogType varchar(50)')    
  exec ('alter table Logs_'+@table+' add LogDate datetime') 
 end

 --**************记录日志
 select * into #deleted from deleted --修改记录转为为临时表
 declare @logType varchar(50),@columns varchar(4000),@sql varchar(4000)
 --获取操作类型
 set @logType='''delete'''
 select @logType='''update''' from inserted
 --组合执行语句
 select @columns=isnull(@columns+',','')+name from syscolumns where id =object_id(@table)
 set @sql='insert into dbo.Logs_'+@table+'(LogDate,LogType,'+@columns+') select getdate(),'+@logType+','+@columns+' from #deleted'
 exec(@sql)
 
 SET NOCOUNT OFF; 
END