如何实现在SQL Server中将Update修改前后的值记录到审计表

OUTPUT子句需通过DELETED（旧值）和INSERTED（新值）伪表联合实现变更审计；二者在UPDATE中同时存在，行数一一对应，但OUTPUT不能直接对比或调用函数，须借助表变量+CONVERT+INTO完成多列变更记录。

如何实现在sql server中将update修改前后的值记录到审计表_使用output子句

SQL Server UPDATE 用 OUTPUT 记录变更前后值的正确写法

直接结论：OUTPUT 子句本身**不能直接访问修改前的旧值和修改后的新值在同一行中做对比式输出**，但可以通过 INSERTED 和 DELETED 伪表联合实现——前提是 UPDATE 语句本身能触发二者同时存在（即非 INSERT/DELETE 场景下，DELETED 存旧值，INSERTED 存新值）。

为什么 UPDATE 的 OUTPUT 能拿到 DELETED 和 INSERTED

SQL Server 在执行 UPDATE 时，底层实际是「先删后插」的逻辑（即使主键未变），所以 OUTPUT 可以引用两个伪表：

DELETED：包含更新前的整行数据（对应 WHERE 条件匹配的原始行）
INSERTED：包含更新后的整行数据（新值，含被 SET 修改的列及其他未改列）
二者行数严格一致，且按执行顺序一一对应（无隐式排序保证，但实践中通常与原表扫描顺序一致）

审计表设计与 OUTPUT 插入的实操要点

假设审计表为 AuditLog，需记录操作时间、表名、主键、字段名、旧值、新值、操作人：

CREATE TABLE AuditLog (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(128),
    PrimaryKey NVARCHAR(200),
    ColumnName NVARCHAR(128),
    OldValue NVARCHAR(MAX),
    NewValue NVARCHAR(MAX),
    UpdatedBy NVARCHAR(128),
    UpdatedAt DATETIME2 DEFAULT GETDATE()
);

关键限制和应对方式：

OUTPUT 不能直接调用函数如 GETDATE() 或变量，必须靠外部提供上下文（如把 @UpdatedBy 作为参数传入）
要记录多列变更，不能只写 OUTPUT INSERTED.Id, DELETED.Name, INSERTED.Name —— 这只能固定列；更通用做法是用 OUTPUT INTO 写入临时表或表变量，再 unpivot 处理
字符串类型建议统一用 CONVERT(NVARCHAR(MAX), ...) 避免隐式转换截断或报错
OUTPUT INTO 目标表必须结构兼容，列数、类型、空值性都要匹配（例如 DELETED.Value 是 INT，目标列也得是可接受 INT 的类型）

一个可运行的完整示例（单列变更 + 审计写入）

场景：更新用户姓名，并将变更写入 AuditLog 表：

DECLARE @UpdatedBy NVARCHAR(128) = 'admin';
DECLARE @AuditTable TABLE (
    TableName NVARCHAR(128),
    PrimaryKey NVARCHAR(200),
    ColumnName NVARCHAR(128),
    OldValue NVARCHAR(MAX),
    NewValue NVARCHAR(MAX),
    UpdatedBy NVARCHAR(128)
);
<p>UPDATE Users 
SET Name = 'Alice Chen'
OUTPUT 
'Users',
CONVERT(NVARCHAR(200), INSERTED.Id),
'Name',
CONVERT(NVARCHAR(MAX), DELETED.Name),
CONVERT(NVARCHAR(MAX), INSERTED.Name),
@UpdatedBy
INTO @AuditTable;</p><p>INSERT INTO AuditLog (TableName, PrimaryKey, ColumnName, OldValue, NewValue, UpdatedBy)
SELECT * FROM @AuditTable;</p>

注意点：