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

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

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

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

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, 1) NOT NULL,
     [ AuditType ] [ char ]( 1) NOT NULL,
     [ TableName ] [ nvarchar ]( 128) NOT NULL,
     [ FieldName ] [ nvarchar ]( 128) NULL,
     [ OldValue ] [ nvarchar ]( 4000) NULL,
     [ NewValue ] [ nvarchar ]( 4000) NULL,
     [ UserName ] [ nvarchar ]( 128) NULL,
     [ 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   = ON) ON [ 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的内部临时触发表

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

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表的过程，写成一个存储过程：

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)

秒客网

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

相关文章