如何在Microsoft SQL Server中用triggers记录表列变更至指定格式表？

1. 创建变更记录表 #

首先创建符合需求格式的变更记录表，用于存储所有变更信息：

CREATE TABLE dbo.ChangeLog (
    ChangeLogID INT IDENTITY(1,1) PRIMARY KEY, -- 可选，添加自增主键便于管理
    TableName NVARCHAR(128) NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL,
    OldValue SQL_VARIANT,
    NewValue SQL_VARIANT,
    RowID SQL_VARIANT NOT NULL, -- 兼容不同类型的主键
    ChangeDate DATETIME2(3) DEFAULT SYSUTCDATETIME(), -- 记录UTC变更时间
    ChangedBy NVARCHAR(128) DEFAULT SUSER_SNAME() -- 记录变更操作的用户
);

2. 为目标表创建UPDATE触发器（以Company表为例） #

针对Company表创建AFTER UPDATE触发器，利用inserted和deleted魔法表捕获新旧值，并关联系统表匹配列名：

CREATE TRIGGER dbo.trg_Company_AfterUpdate
ON dbo.Company
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 拆解inserted和deleted表为列级数据
    WITH InsertedColumns AS (
        SELECT 
            i.RowID,
            c.ColumnName,
            CAST(i.Value AS SQL_VARIANT) AS NewValue
        FROM inserted i
        CROSS APPLY (
            VALUES
                (i.RowID, 'CompName', i.CompName),
                (i.RowID, 'Address', i.Address)
                -- 继续添加Company表的其他列，格式：(RowID, '列名', 列值)
        ) AS i(RowID, ColumnName, Value)
    ),
    DeletedColumns AS (
        SELECT 
            d.RowID,
            c.ColumnName,
            CAST(d.Value AS SQL_VARIANT) AS OldValue
        FROM deleted d
        CROSS APPLY (
            VALUES
                (d.RowID, 'CompName', d.CompName),
                (d.RowID, 'Address', d.Address)
                -- 同上，添加其他列
        ) AS d(RowID, ColumnName, Value)
    )

    -- 插入变更记录（仅保存值发生变化的列）
    INSERT INTO dbo.ChangeLog (TableName, ColumnName, OldValue, NewValue, RowID)
    SELECT 
        'Company' AS TableName,
        ic.ColumnName,
        dc.OldValue,
        ic.NewValue,
        ic.RowID
    FROM InsertedColumns ic
    INNER JOIN DeletedColumns dc 
        ON ic.RowID = dc.RowID AND ic.ColumnName = dc.ColumnName
    WHERE 
        ic.NewValue <> dc.OldValue
        OR (ic.NewValue IS NULL AND dc.OldValue IS NOT NULL)
        OR (ic.NewValue IS NOT NULL AND dc.OldValue IS NULL);
END;

3. 通用化触发器生成（可选） #

如果需要为多个表创建类似触发器，可使用动态SQL生成脚本，避免重复手动编写：

CREATE PROCEDURE dbo.GenerateChangeLogTrigger
    @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取目标表的单主键列（复合主键需调整逻辑）
    DECLARE @PrimaryKey NVARCHAR(128);
    SELECT @PrimaryKey = c.name
    FROM sys.key_constraints kc
    JOIN sys.columns c ON kc.parent_object_id = c.object_id 
        AND c.column_id = (SELECT TOP 1 column_id FROM sys.index_columns WHERE object_id = kc.parent_object_id AND index_id = kc.unique_index_id)
    WHERE kc.type = 'PK' AND kc.parent_object_id = OBJECT_ID(@TableName);

    -- 拼接目标表所有列的VALUES子句
    DECLARE @Columns NVARCHAR(MAX);
    SELECT @Columns = STRING_AGG(FORMAT('({0}, ''{1}'', {1})', @PrimaryKey, name), ',')
    FROM sys.columns WHERE object_id = OBJECT_ID(@TableName);

    -- 生成触发器脚本
    DECLARE @TriggerSQL NVARCHAR(MAX) = N'
    CREATE TRIGGER dbo.trg_' + @TableName + '_AfterUpdate
    ON dbo.' + @TableName + '
    AFTER UPDATE
    AS
    BEGIN
        SET NOCOUNT ON;

        WITH InsertedColumns AS (
            SELECT 
                i.' + @PrimaryKey + ' AS RowID,
                c.ColumnName,
                CAST(i.Value AS SQL_VARIANT) AS NewValue
            FROM inserted i
            CROSS APPLY (
                VALUES
                    ' + @Columns + '
            ) AS i(RowID, ColumnName, Value)
        ),
        DeletedColumns AS (
            SELECT 
                d.' + @PrimaryKey + ' AS RowID,
                c.ColumnName,
                CAST(d.Value AS SQL_VARIANT) AS OldValue
            FROM deleted d
            CROSS APPLY (
                VALUES
                    ' + @Columns + '
            ) AS d(RowID, ColumnName, Value)
        )

        INSERT INTO dbo.ChangeLog (TableName, ColumnName, OldValue, NewValue, RowID)
        SELECT 
            ''' + @TableName + ''' AS TableName,
            ic.ColumnName,
            dc.OldValue,
            ic.NewValue,
            ic.RowID
        FROM InsertedColumns ic
        INNER JOIN DeletedColumns dc 
            ON ic.RowID = dc.RowID AND ic.ColumnName = dc.ColumnName
        WHERE 
            ic.NewValue <> dc.OldValue
            OR (ic.NewValue IS NULL AND dc.OldValue IS NOT NULL)
            OR (ic.NewValue IS NOT NULL AND dc.OldValue IS NULL);
    END;';

    -- 执行生成的触发器脚本
    EXEC sp_executesql @TriggerSQL;
END;

使用方式：

-- 为Company表生成变更记录触发器
EXEC dbo.GenerateChangeLogTrigger 'Company';

关键说明 #

• SQL_VARIANT类型用于兼容不同数据类型的列值和主键，确保变更记录能存储任意类型的数据。
• 触发器仅记录值发生变化的列，避免冗余记录。
• 通用化存储过程假设目标表有单一主键，复合主键场景需调整脚本逻辑。
• 可根据需求排除不需要追踪的列，减少性能开销。

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