修改UPDATE触发器：为每个更新字段插入日志记录

我现有一个仅在Manager字段更新时记录日志的触发器，需要修改它，使其为每一个被更新的字段都插入一条日志记录。例如执行语句UPDATE Test SET Manager='A', Location='B', Department='D';时，需在test_UpdateLog表中插入3条记录。当前触发器代码如下：

ALTER TRIGGER [dbo].[TR_test_UpdateLog]
ON [dbo].[test]
AFTER UPDATE
AS
BEGIN

    INSERT INTO dbo.test_UpdateLog(TableName,PrimaryKeyValue,ColumnName,OldValue,NewValue,UpdatedBy)
      SELECT 'test',i.Email,'Manager', d.Manager, i.Manager,USER_NAME()
      FROM Inserted i
      INNER JOIN Deleted d ON i.Email = d.Email
      WHERE d.Manager <> i.Manager

END;

要实现每个更新字段单独生成日志，你可以通过UNION ALL拆分每个字段的日志逻辑，每个字段对应一段独立的查询，仅在该字段新旧值不同时生成记录。这样多个字段更新时，每个变化的字段都会插入一条独立日志。

修改后的触发器代码：

ALTER TRIGGER [dbo].[TR_test_UpdateLog]
ON [dbo].[test]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回额外的行数统计信息

    INSERT INTO dbo.test_UpdateLog(TableName, PrimaryKeyValue, ColumnName, OldValue, NewValue, UpdatedBy)
    -- 处理Manager字段的更新日志
    SELECT 
        'test', 
        i.Email, 
        'Manager', 
        CAST(d.Manager AS VARCHAR(MAX)),
        CAST(i.Manager AS VARCHAR(MAX)),
        USER_NAME()
    FROM Inserted i
    INNER JOIN Deleted d ON i.Email = d.Email
    WHERE d.Manager <> i.Manager

    UNION ALL

    -- 处理Location字段的更新日志
    SELECT 
        'test', 
        i.Email, 
        'Location', 
        CAST(d.Location AS VARCHAR(MAX)),
        CAST(i.Location AS VARCHAR(MAX)),
        USER_NAME()
    FROM Inserted i
    INNER JOIN Deleted d ON i.Email = d.Email
    WHERE d.Location <> i.Location

    UNION ALL

    -- 处理Department字段的更新日志
    SELECT 
        'test', 
        i.Email, 
        'Department', 
        CAST(d.Department AS VARCHAR(MAX)),
        CAST(i.Department AS VARCHAR(MAX)),
        USER_NAME()
    FROM Inserted i
    INNER JOIN Deleted d ON i.Email = d.Email
    WHERE d.Department <> i.Department;
END;

核心细节说明 #

• UNION ALL的作用：把每个字段的日志记录合并后批量插入，比多次执行INSERT效率更高，也能保证原子性。
• 类型转换：用CAST(字段 AS VARCHAR(MAX))统一转成字符串类型，确保不同数据类型的字段（比如整数、字符串、日期等）都能适配日志表的OldValue/NewValue字段（假设这两个字段是字符串类型）。如果日志表字段类型特殊，可根据实际调整转换规则。
• SET NOCOUNT ON：防止触发器执行时返回“影响了X行”的额外信息，避免干扰调用更新语句的应用程序逻辑。
• WHERE条件过滤：只在字段新旧值确实不同时生成日志，排除无意义的空更新操作。

如果后续需要监控更多字段，直接按照现有格式添加新的UNION ALL分支即可。

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