记录DATA数据修正操作日志(测试版)V1.0

一、需求 ：

　　 怎么样记录那些，通过SQL脚本在后台数据库操作的动作。目的是，方便未来，可能因某一历史的直接后台脚本操作导致一些问题出现，能够帮忙我们，快速检索哪一次后台操作脚本有问题。

二、分析 ：

 　　根据需要，我们可以提取有用的信息，

 　　　　1.　　应用数据库环境：   后台数据库

　　 　　2.　　记录粒度（对象）：各数据库的表，行

            3.　　操作对象： 　　　　在后台执行的SQL脚本&操作人  

 　　

三、设计 :

 　　　　根据上面的需要和分析，找到两个解决方案：

　　　　　　a. 每次执行SQL脚本时候，对数据库影响的记录先导入到一个新表，作为备份方便日后问题追踪。这方案，优点是针对数据行的Insert,Update，Delete，可以这样做备份表里面的数据，缺点是每次对数据库表操作

都有对应操作的数据库备份表，一个表对应多个备份表，时间一长，备份表就越来越多，有一天，会惊异：“我的上帝，怎么这么多表，晕了我”，我们又不得不去删除&整理历史的备份表。

　　　　　　b.使用触发器。对影响到表，创建触发器，把影响到的记录行自动备份到日志表中，方便日后问题追踪。

           第2个方案，是我今天要用到的方案。针对这一方案的，　我分成三个过程来说明，感觉这样比较清晰，容易明白。

 　  1. 数据表设计

　　　　　　这里分为两个版本，一个是测试版，也就是当前我写的这一篇用到的版本。还有一个扩展版，也就是在测试版，能够实现&稳定基础上，派生出来的一个版本，它更能方便我们的管理，和问题追踪。O(∩_∩)O~，这是future来的了。

          

　　　　测试版应用在每一个数据库中，测试版的E-R图：

 　　　　

           未来扩展版，将是部署在独立个一个DB管理数据库中(如数据库名为[_ADMIN]),对实例下各个数据库的后台脚本操作动作都记录在同一个库[_ADMIN]中。这样就能方便管理，及更高一层的扩展，如图：‘

          

　　2.脚本编写

         创建表脚本：

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

　　3.脚本调式

     

/*    以下是測試調用過程：    */

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

*/

 查询结果：

 

 (测试版编写完成)

No incapable except unthinkable.                 

MSN:weiguohao008@hotmail.com