判断字段是否被更新 新旧数据写入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
```