客户要求,要对一个敏感数据表进行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
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
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)
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)