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

客户要求对于敏感数据表进行详尽的Audit跟踪，确保在数据更新时，能够捕捉到旧值与更新后的新值，以及更改记录的用户和更新时间等信息。面对这一需求，我们提出了一套完善的解决方案。

为了灵活应对客户可能随时变更的跟踪字段需求，我们创建了一个表来决定哪些表和字段需要被跟踪。接着，我们创建了一个名为[Audit]的表来存储跟踪记录。这一设计极大地提高了系统的灵活性和可扩展性。

以下是我们的数据库表结构构建代码：

```sql

-- 开启SQL设置

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

SET ANSI_PADDING ON;

-- 创建Audit表

CREATE TABLE [dbo].[Audit] (

[Audit_nbr] [int] IDENTITY(1,1) NOT NULL,

[TableName] [nvarchar](128) NOT NULL, -- 被跟踪的表名

[FieldName] [nvarchar](128) NULL, -- 需要跟踪的字段名

[OldValue] [nvarchar](0) NULL, -- 旧的字段值

[NewValue] [nvarchar](0) NULL, -- 更新后的字段值

[UserName] [nvarchar](128) NULL, -- 更新记录的用户名

[CreateDate] [datetime] NOT NULL DEFAULT GETDATE(), -- 记录创建时间

PRIMARY KEY CLUSTERED ([Audit_nbr] ASC)

);

-- 为Audit表中的AuditType添加约束，确保其只能是'D', 'U', 'I'其中之一

ALTER TABLE [dbo].[Audit] WITH CHECK ADD CHECK ([AuditType] IN ('D', 'U', 'I'));

-- 接下来，我们将为跟踪表创建一个更新触发器。这个触发器会在数据更新时自动执行。在尝试访问内部临时触发表INSERTED和DELETED时，可能会遇到异常。为了解决这个问题，我们可以参考以下的触发器代码：

```sql

-- 创建更新触发器

CREATE TRIGGER trg_DataUpdateAudit ON YourTableName -- 替换YourTableName为目标表名

AFTER UPDATE

AS

BEGIN

-- 设置变量以存储新旧数据值以及用户需要跟踪的字段名

DECLARE @sql NVARCHAR(MAX), @N NVARCHAR(MAX), @O NVARCHAR(MAX); --@N和@O用于存储新旧数据值，@I用于存储用户需要跟踪的字段名

SET @I = '需要跟踪的字段名'; -- 这里需要替换为实际要跟踪的字段名

SET @sql = N'SELECT @N = [' + @I + '] FROM inserted'; -- 从inserted表获取更新后的数据值

EXECUTE sp_executesql @sql, N'@N NVARCHAR(MAX) OUTPUT', @N OUTPUT;

SET @sql = N'SELECT @O = [' + @I + '] FROM deleted'; -- 从deleted表获取原始数据值

EXECUTE sp_executesql @sql, N'@O NVARCHAR(MAX) OUTPUT', @O OUTPUT;

IF (@N != @O)

INSERT INTO Audit (AuditType, TableName, FieldName, OldValue, NewValue) VALUES ('U', 'YourTableName', @I, @O, @N); -- 替换YourTableName为目标表名，并替换其他相关字段的实际值。同时确保其他字段如UserName和CreateDate也被正确填充。

END;

GO;

```sql

存储过程的代码如下：

```sql

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_Audit_Insert]

(

@TableName NVARCHAR(128), -- 被操作的表名

@FieldName NVARCHAR(128), -- 被操作的字段名

@OldValue NVARCHAR(0), -- 旧值

@NewValue NVARCHAR(0), -- 新值

@UserName NVARCHAR(128) -- 操作的用户名

)

AS

BEGIN

INSERT INTO [dbo].[Audit]

([AuditType],[TableName],[FieldName],[OldValue],[NewValue],[UserName])

VALUES (@AuditType,@TableName,@FieldName,@OldValue,@NewValue,@UserName)

END

GO

```