操作日志

 

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

*/