判断字段是否被更新,新旧数据写入Audit Log表中

时间:2021-07-13 07:25:27

客户要求,要对一个敏感数据表进行Audit跟踪。如果记录被更新时,要把旧新保存起来,是谁更改了记录,什么时候更新的等相关信息。还有一个主要问题就是客户不确定具体要跟踪那个字段,希望自己决定。

Insus.NET解决这个问题,只有创建另外一个表,将存储用户决定要跟踪的表,以及这个表中需要跟踪的字段。

还要创建另外一个表[Audit],就是存储跟踪记录的表:

判断字段是否被更新,新旧数据写入Audit Log表中判断字段是否被更新,新旧数据写入Audit Log表中Audit
SET ANSI_NULLS  ON
GO

SET QUOTED_IDENTIFIER  ON
GO

SET ANSI_PADDING  ON
GO

CREATE  TABLE  [ dbo ]. [ Audit ](
     [ Audit_nbr ]  [ int ]  IDENTITY( 1, 1NOT  NULL,
     [ AuditType ]  [ char ]( 1NOT  NULL,
     [ TableName ]  [ nvarchar ]( 128NOT  NULL,    
     [ FieldName ]  [ nvarchar ]( 128NULL,
     [ OldValue ]  [ nvarchar ]( 4000NULL,
     [ NewValue ]  [ nvarchar ]( 4000NULL,
     [ UserName ]  [ nvarchar ]( 128NULL,
     [ CreateDate ]  [ datetime ]  NOT  NULL,
PRIMARY  KEY  CLUSTERED 
(
     [ Audit_nbr ]  ASC
) WITH (PAD_INDEX   =  OFF, STATISTICS_NORECOMPUTE   =  OFF, IGNORE_DUP_KEY  =  OFF, ALLOW_ROW_LOCKS   =  ON, ALLOW_PAGE_LOCKS   =  ONON  [ PRIMARY ]
ON  [ PRIMARY ]

GO

SET ANSI_PADDING  OFF
GO

ALTER  TABLE  [ dbo ]. [ Audit ]   WITH  CHECK  ADD  CHECK  (( [ AuditType ] = ' D '  OR  [ AuditType ] = ' U '  OR  [ AuditType ] = ' I '))
GO

ALTER  TABLE  [ dbo ]. [ Audit ]  ADD   DEFAULT ( getdate())  FOR  [ CreateDate ]
GO

 

解决是谁更新数据,是使用这个方法:在SQL触发器或存储过程中获取在程序登录的用户

接下来,为跟踪表写一个更新Trigger触发器。

在触发器中访问INSERTED或DELETED的内部临时触发表,会得一个异常invalid object name 'inserted' 或是invalid object name 'deleted' ,解决此问题,可以参考这篇:

EXEC(EXECUTE)函数访问INSERTED或DELETED的内部临时触发表

 下面为表更新触发器(部分),有注释:

判断字段是否被更新,新旧数据写入Audit Log表中判断字段是否被更新,新旧数据写入Audit Log表中View Code
-- @N和@O两个变量,一个存储更新数据值,一个为原有数据值
                 DECLARE  @sql  NVARCHAR( MAX), @N  DECIMAL( 18, 0), @O  DECIMAL( 18, 0)    
                
                 -- @I变量是用户需要跟踪的字段             
                 SET  @sql  = N ' SELECT @N = [ ' +  CONVERT( NVARCHAR( MAX), @I+ ' ] FROM #inserted '
                
                 -- 执行动态SQL语句。
                 EXECUTE sp_executesql  @sql,
                                      N ' @N DECIMAL(18,0) OUTPUT ',
                                       @N OUTPUT;
                                      
                 -- 下面SQL代码,是从deleted表中获取原来数据值。                                      
                 SET  @sql  = N ' SELECT @O = [ ' +  CONVERT( NVARCHAR( MAX), @I+ ' ] FROM #deleted '        
                 EXECUTE sp_executesql  @sql,
                                      N ' @O DECIMAL(18,0) OUTPUT ',
                                       @O OUTPUT;
                
                 -- 对比两个数据值,更新值与原有值,如果不一样,把数据插入Audit Log表中。                
                 IF ( ISNULL( @N, 0<>  ISNULL( @O, 0))                
                     EXECUTE  [ dbo ]. [ usp_Audit_Insert ]  ' U ', ' <tableName> ', ' <fieldName> ', @O, @N, @UserName

 

 上面代码还有一个存储过程,原因是如果多表或是一个表有更新或是删除需要把跟踪的数据插入Audit Log表中时,为了更好维护与代码冗余,因此把插入Audit Log表的过程,写成一个存储过程:

判断字段是否被更新,新旧数据写入Audit Log表中判断字段是否被更新,新旧数据写入Audit Log表中usp_Audit_Insert
SET ANSI_NULLS  ON
GO
SET QUOTED_IDENTIFIER  ON
GO
ALTER  PROCEDURE  [ dbo ]. [ usp_Audit_Insert ]
(
     @AuditType  [ char ]( 1),
     @TableName  [ nvarchar ]( 128),
     @FieldName  [ nvarchar ]( 128),    
     @OldValue  [ nvarchar ]( 4000),
     @NewValue  [ nvarchar ]( 4000),
     @UserName  [ nvarchar ]( 128)
)
AS
INSERT  INTO  [ dbo ]. [ Audit ]
    ( [ AuditType ], [ TableName ], [ FieldName ], [ OldValue ], [ NewValue ], [ UserName ])
VALUES
    ( @AuditType, @TableName, @FieldName, @OldValue, @NewValue, @UserName)