if object_id('DATA_LogOfDBOperation') Is Null /*Data数据修正操作主表*/ Begin Create Table DATA_LogOfDBOperation ( ID uniqueidentifier Not Null Default(newid()) rowguidcol, Logdate datetime Not Null default(Getdate()), Operator nvarchar(50), Note nvarchar(200), Constraint PK_DATA_LogOfDBOperation Primary Key(ID Asc) ) End if object_id('DATA_LogDetailOfDBOperation') Is Null /*Data数据修正操作明细表*/ Begin Create Table DATA_LogDetailOfDBOperation ( ID uniqueidentifier Not Null Default(newid()) rowguidcol, DATA_LogOfDBOperationID uniqueidentifier Not Null, TableName sysname, Description nvarchar(max), OperationType nvarchar(50), Flag bit, Constraint PK_DATA_LogDetailOfDBOperation Primary Key(ID Asc), Constraint FK_DATA_LogDetailOfDBOperation_DATA_LogOfDBOperationID Foreign Key (DATA_LogOfDBOperationID) References DATA_LogOfDBOperation(ID) ) Create Nonclustered Index IX_DATA_LogDetailOfDBOperation_DATA_LogOfDBOperationID On DATA_LogDetailOfDBOperation (DATA_LogOfDBOperationID Asc) Create Nonclustered Index IX_DATA_LogDetailOfDBOperation_TableName On DATA_LogDetailOfDBOperation (TableName Asc) End
if object_id('sp_CreateTriggerWithAuto') Is Not Null Drop Proc sp_CreateTriggerWithAuto Go Create Proc sp_CreateTriggerWithAuto ( @TableList nvarchar(max), @DorpTriggerStr nvarchar(max) output ) As Declare @Sql nvarchar(max), @str nvarchar(max), @ObjectName nvarchar(128) Set @str=N' Create trigger tr_%ObjectName%_temp On %ObjectName% After Insert,update,delete As Declare @Data nvarchar(Max), @Type char(6), @Table nvarchar(128), @Cols nvarchar(max), @Sql nvarchar(max) /*提取表字段内容,这里不做资料类型判断*/ Select @Table=''%ObjectName%'', @Data='''', @Cols='''' Select @Cols=+@Cols+'''''' ''+name+''=''''+Convert(nvarchar(max),Isnull(Quotename(''+name+'',''''"''''),''''null''''))+'' From sys.columns where object_id=object_id(@Table) Set @Cols=left(@Cols,len(@Cols)-1) /*判断操作类型,Insert/Update/Delete*/ If Exists(Select 1 From inserted) And Not Exists(Select 1 From deleted) Set @Type=''Insert'' Else If exists(Select 1 From Inserted) And Exists(Select 1 From deleted) Set @Type=''Update'' Else Set @Type=''Delete'' /*写入日志表*/ Begin --读更新前后的数据 If Object_id(''tempdb..#TmpTrigger1'') Is Not Null Drop table #TmpTrigger1 Select *,TriggerKeyFlag=0 into #TmpTrigger1 From deleted union all Select *,TriggerKeyFlag=1 From inserted /* 构造的SQL语句,暂时不考虑以下情况: 1. 这里不考虑开发的处理,所以取对应日志主表(DATA_LogOfDBOperation)的ID时候,读的是最新的ID, 在目前环境中,并发的可能性很小,要是以后应用于并发环境,需要重新修改这一位置 2. 当日志表在独立一个库时候,以下的语句不适用. */ Set @Sql=N''Insert Into DATA_LogDetailOfDBOperation (DATA_LogOfDBOperationID,TableName,Description,OperationType,Flag) Select (Select Top(1) ID From DATA_LogOfDBOperation Order By Logdate Desc) , @table,''+@Cols+'',@Type,TriggerKeyFlag From #TmpTrigger1'' --执行SQL语句 exec sp_executesql @Sql,N''@table nvarchar(128),@Type nvarchar(max)'',@table,@Type End ' Set @TableList=@TableList+',' Set @DorpTriggerStr='' While @TableList>'' /*根据提供的Table列表,创建对应Table的Trigger*/ Begin Set @ObjectName=substring(@TableList,1,Charindex(N',',@TableList)-1) If @ObjectName>'' Begin /*构造删除Trgger语句,为过程发生错误的时候调用*/ Set @DorpTriggerStr=@DorpTriggerStr+Char(13)+Char(10)+'If object_id(''tr_'+@ObjectName+'_temp'') Is Not Null Drop Trigger tr_'+@ObjectName+'_temp' Set @Sql=Replace(@Str,'%ObjectName%',@ObjectName) /*先删除之前创建的Trigger语句,以防发生错误*/ Exec('If object_id(''tr_'+@ObjectName+'_temp'') Is Not Null Drop Trigger tr_'+@ObjectName+'_temp') /*创建 Trigger*/ Exec(@Sql) End Set @TableList=stuff(@TableList,1,Charindex(N',',@TableList),'') End Goto SubExit ErrorExit: --错误处理Drop Trigger If @DorpTriggerStr>'' Exec(@DorpTriggerStr) Set @DorpTriggerStr='' SubExit: Go
--0.准备一张表test If object_id('test') Is Not Null Drop Table test GO Select * Into test From master.sys.all_objects Go --1创建Trigger Declare @DorpTriggerStr nvarchar(max) Exec sp_CreateTriggerWithAuto 'test',@DorpTriggerStr output --2.登记操作日志 Insert Into DATA_LogOfDBOperation( ID ,Logdate ,Operator ,Note) Select newid(),getdate(),suser_name(),N'Data数据操作测试' --3.对表操作动作 ;With CTE_Test As ( Select Top 2 * From test ) Delete From CTE_Test --4. 删除Trigger If @DorpTriggerStr>'' Exec(@DorpTriggerStr) Go Select * From DATA_LogOfDBOperation Select * From DATA_LogDetailOfDBOperation /* Drop Table DATA_LogDetailOfDBOperation Drop Table DATA_LogOfDBOperation */