SQL Server 2019字段变更行级日志触发器创建需求

先修正历史表结构 #

原history_notizen_aktionen表的主键设置有误——单条记录的多个字段变更会生成多条日志，无法用notiz_aktion_id作为唯一主键，需调整主键为自增的history_id：

ALTER TABLE history_notizen_aktionen
DROP CONSTRAINT PK__history___2B82835CF2EB6029;

ALTER TABLE history_notizen_aktionen
ADD CONSTRAINT PK_history_notizen_aktionen PRIMARY KEY (history_id);

创建字段级变更日志触发器 #

以下触发器会在notizen_aktionen表发生更新时，为每个变更的字段单独插入一行日志记录：

CREATE TRIGGER trg_notizen_aktionen_field_history
ON notizen_aktionen
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 记录notiz_id字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'notiz_id',
        CAST(d.notiz_id AS TEXT),
        CAST(i.notiz_id AS TEXT)
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE i.notiz_id <> d.notiz_id;

    -- 记录aktion_id字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'aktion_id',
        CAST(d.aktion_id AS TEXT),
        CAST(i.aktion_id AS TEXT)
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE i.aktion_id <> d.aktion_id;

    -- 记录empfaenger_id字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'empfaenger_id',
        CAST(d.empfaenger_id AS TEXT),
        CAST(i.empfaenger_id AS TEXT)
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE i.empfaenger_id <> d.empfaenger_id;

    -- 记录anlage_user_id字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'anlage_user_id',
        CAST(d.anlage_user_id AS TEXT),
        CAST(i.anlage_user_id AS TEXT)
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE i.anlage_user_id <> d.anlage_user_id;

    -- 记录beschreibung字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'beschreibung',
        d.beschreibung,
        i.beschreibung
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE ISNULL(i.beschreibung, '') <> ISNULL(d.beschreibung, '');

    -- 记录status_id字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'status_id',
        CAST(d.status_id AS TEXT),
        CAST(i.status_id AS TEXT)
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE i.status_id <> d.status_id;

    -- 记录email_gesendet字段变更
    INSERT INTO history_notizen_aktionen (notiz_aktion_id, field_name, old_value, new_value)
    SELECT 
        i.notiz_aktion_id,
        'email_gesendet',
        CAST(d.email_gesendet AS TEXT),
        CAST(i.email_gesendet AS TEXT)
    FROM inserted i
    JOIN deleted d ON i.notiz_aktion_id = d.notiz_aktion_id
    WHERE i.email_gesendet <> d.email_gesendet;
END;

触发器说明 #

1. 针对notizen_aktionen表的每个字段单独判断：仅当字段的新旧值不同时，才会向历史表插入一条日志
2. 所有非文本类型的字段都转换为TEXT类型，匹配历史表的字段类型
3. 对于允许为空的字段（如beschreibung），用ISNULL处理空值比较，避免因NULL导致的比较失效
4. 历史表的timestamp字段会自动取当前时间，无需手动赋值

生成用户友好的变更展示 #

若要在应用中展示类似示例的格式，可以通过如下查询获取数据：

SELECT 
    FORMAT(h.[timestamp], 'yyyy-MM-dd') + ' - ' + 
    CASE h.field_name
        WHEN 'status_id' THEN '状态从 ' + h.old_value + ' 变更为 ' + h.new_value
        WHEN 'email_gesendet' THEN '邮件发送状态从 ' + h.old_value + ' 变更为 ' + h.new_value
        WHEN 'beschreibung' THEN '描述从 ''' + h.old_value + ''' 变更为 ''' + h.new_value + ''''
        ELSE REPLACE(h.field_name, '_', ' ') + ' 从 ' + h.old_value + ' 变更为 ' + h.new_value
    END AS change_log
FROM history_notizen_aktionen h
ORDER BY h.[timestamp] DESC;

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