判断字段是否被更新 新旧数据写入Audit Log表中
判断字段是否被更新 新旧数据写入Audit Log表中
发布时间:2016-12-29 来源:查字典编辑
摘要:Insus.NET解决这个问题,只有创建另外一个表,将存储用户决定要跟踪的表,以及这个表中需要跟踪的字段。还要创建另外一个表[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的内部临时触发表

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

复制代码 代码如下:

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

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新mssql数据库学习
热门mssql数据库学习
编程开发子分类