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