SQL Server触发器优化需求：判断字段值实际变更并生成全量日志

嘿，这个问题我之前帮不少朋友解决过，咱们一步步来拆解实现思路，既能满足「仅记录实际变更」的需求，又能搞定「字段值拼接」的要求：

咱们要解决两个核心问题：仅记录实际发生值变更的操作，以及拼接更新后的字段值为字符串。针对不同的操作类型（Insert/Update/Delete），处理逻辑略有区别：

• Insert：所有字段都是新增值，直接记录即可
• Delete：记录被删除的全量字段值
• Update：需要逐个对比INSERTED和DELETED表的字段值，只有当值真正不同时，才触发日志记录，同时拼接更新后的有效字段值

1. 确认日志表结构（如需调整） #

假设你的日志表叫OperationLogs，建议包含这些核心字段（可根据实际场景扩展）：

CREATE TABLE OperationLogs (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(100) NOT NULL, -- 操作的目标表名
    OperationType NVARCHAR(10) NOT NULL, -- 操作类型：INSERT/UPDATE/DELETE
    ChangedValues NVARCHAR(MAX) NOT NULL, -- 拼接后的字段值字符串
    Operator NVARCHAR(50) NOT NULL, -- 操作人，可根据实际场景获取（比如SESSION_USER或UI传递的用户ID）
    OperationTime DATETIME DEFAULT GETDATE() -- 操作时间
)

2. 单表触发器示例（以YourTable为例） #

下面是针对YourTable的完整触发器，重点看Update部分的字段对比逻辑：

CREATE TRIGGER trg_YourTable_OperationLog
ON YourTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(100) = 'YourTable';
    DECLARE @Operator NVARCHAR(50) = SESSION_USER; -- 这里可替换为你的用户获取方式
    DECLARE @ChangedValues NVARCHAR(MAX);
    DECLARE @OperationType NVARCHAR(10);

    -- 处理INSERT操作
    IF EXISTS(SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @OperationType = 'INSERT';
        -- 用JSON解析拼接所有插入字段的键值对
        SELECT @ChangedValues = STUFF(
            (SELECT ',' + CONCAT(COLUMN_NAME, '=', QUOTENAME(CAST(VALUE AS NVARCHAR(MAX)), ''''))
             FROM INSERTED i
             CROSS APPLY (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS j
             FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

        INSERT INTO OperationLogs(TableName, OperationType, ChangedValues, Operator)
        VALUES(@TableName, @OperationType, @ChangedValues, @Operator);
    END
    -- 处理DELETE操作
    ELSE IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
    BEGIN
        SET @OperationType = 'DELETE';
        -- 拼接所有删除字段的键值对
        SELECT @ChangedValues = STUFF(
            (SELECT ',' + CONCAT(COLUMN_NAME, '=', QUOTENAME(CAST(VALUE AS NVARCHAR(MAX)), ''''))
             FROM DELETED d
             CROSS APPLY (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS j
             FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

        INSERT INTO OperationLogs(TableName, OperationType, ChangedValues, Operator)
        VALUES(@TableName, @OperationType, @ChangedValues, @Operator);
    END
    -- 处理UPDATE操作（核心逻辑）
    ELSE IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @OperationType = 'UPDATE';
        -- 对比新旧字段值，仅保留实际变更的字段
        SELECT @ChangedValues = STUFF(
            (SELECT ',' + CONCAT(j.COLUMN_NAME, '=', QUOTENAME(CAST(j.VALUE AS NVARCHAR(MAX)), ''''))
             FROM INSERTED i
             CROSS APPLY (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS j
             JOIN (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS k ON j.COLUMN_NAME = k.COLUMN_NAME
             -- 重点处理NULL值：SQL中NULL与任何值对比都返回UNKNOWN，必须单独判断
             WHERE (j.VALUE <> k.VALUE) 
                OR (j.VALUE IS NULL AND k.VALUE IS NOT NULL)
                OR (j.VALUE IS NOT NULL AND k.VALUE IS NULL)
             FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

        -- 仅当存在实际变更时才插入日志
        IF @ChangedValues IS NOT NULL
        BEGIN
            INSERT INTO OperationLogs(TableName, OperationType, ChangedValues, Operator)
            VALUES(@TableName, @OperationType, @ChangedValues, @Operator);
        END
    END
END
GO

3. 关键细节说明 #

• NULL值处理：这是最容易踩的坑——SQL中NULL <> 任何值的结果都是UNKNOWN，必须单独判断NULL和非NULL切换的场景，不然会漏掉这类变更记录。
• 动态字段拼接：用FOR JSON PATH转成JSON再解析的方式，不需要硬编码字段名，适配表结构变更，也方便批量处理。
• 批量操作兼容：触发器支持单条和批量操作（比如一次更新多条记录），会自动拼接所有变更行的字段值；如果需要按行单独记录，可以调整逻辑为逐行处理。

4. 批量生成25张表的触发器（偷懒神器） #

如果25张表一个个写触发器太费时间，用动态SQL批量生成就省事多了：

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = @SQL + '
CREATE TRIGGER trg_' + TABLE_NAME + '_OperationLog
ON ' + TABLE_NAME + '
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(100) = ''' + TABLE_NAME + ''';
    DECLARE @Operator NVARCHAR(50) = SESSION_USER;
    DECLARE @ChangedValues NVARCHAR(MAX);
    DECLARE @OperationType NVARCHAR(10);

    IF EXISTS(SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @OperationType = ''INSERT'';
        SELECT @ChangedValues = STUFF(
            (SELECT '','' + CONCAT(COLUMN_NAME, ''='', QUOTENAME(CAST(VALUE AS NVARCHAR(MAX)), ''''''))
             FROM INSERTED i
             CROSS APPLY (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS j
             FOR XML PATH(''''), TYPE
        ).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''');

        INSERT INTO OperationLogs(TableName, OperationType, ChangedValues, Operator)
        VALUES(@TableName, @OperationType, @ChangedValues, @Operator);
    END
    ELSE IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
    BEGIN
        SET @OperationType = ''DELETE'';
        SELECT @ChangedValues = STUFF(
            (SELECT '','' + CONCAT(COLUMN_NAME, ''='', QUOTENAME(CAST(VALUE AS NVARCHAR(MAX)), ''''''))
             FROM DELETED d
             CROSS APPLY (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS j
             FOR XML PATH(''''), TYPE
        ).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''');

        INSERT INTO OperationLogs(TableName, OperationType, ChangedValues, Operator)
        VALUES(@TableName, @OperationType, @ChangedValues, @Operator);
    END
    ELSE IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @OperationType = ''UPDATE'';
        SELECT @ChangedValues = STUFF(
            (SELECT '','' + CONCAT(j.COLUMN_NAME, ''='', QUOTENAME(CAST(j.VALUE AS NVARCHAR(MAX)), ''''''))
             FROM INSERTED i
             CROSS APPLY (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS j
             JOIN (
                 SELECT COLUMN_NAME, VALUE
                 FROM OPENJSON((SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
             ) AS k ON j.COLUMN_NAME = k.COLUMN_NAME
             WHERE (j.VALUE <> k.VALUE) 
                OR (j.VALUE IS NULL AND k.VALUE IS NOT NULL)
                OR (j.VALUE IS NOT NULL AND k.VALUE IS NULL)
             FOR XML PATH(''''), TYPE
        ).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''');

        IF @ChangedValues IS NOT NULL
        BEGIN
            INSERT INTO OperationLogs(TableName, OperationType, ChangedValues, Operator)
            VALUES(@TableName, @OperationType, @ChangedValues, @Operator);
        END
    END
END
GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- 可添加条件过滤你的25张表，比如 TABLE_NAME IN ('Table1', 'Table2', ...)

EXEC sp_executesql @SQL;

内容的提问来源于stack exchange，提问作者B Vidhya