SQL Server触发器使用变量：多字段差异审计消息拼接需求

嘿，我懂你要做的事儿了——你想把原来那个只判断单个字段的INSERT触发器改成能检查多个字段差异，把所有变化的地方拼成一条完整的审计消息插入到aud_Measures表对吧？

首先得说，原来的代码里多次用SELECT TOP 1子查询效率不高，而且CASE逻辑只能返回单个字段的变化，没法拼接多个差异。我给你两种实现方式，优先推荐第一种集合操作的方式，效率更高；如果一定要用变量拼接，可以用第二种游标逐行处理的方式。

方式一：集合操作（推荐） #

这种方式直接通过关联inserted表和上一条记录，用字符串拼接组合所有差异消息，不需要游标，性能更好：

ALTER TRIGGER [dbo].[trg_Measure_Insert_Audit] 
ON [dbo].[Measures_slave] 
AFTER INSERT 
AS 
BEGIN
    SET NOCOUNT ON;

    -- 插入审计记录，关联新插入记录与上一条slave记录
    INSERT INTO aud_Measures (measure_FK, [description], oldname, newname, milestone)
    SELECT 
        i.measure_PK,
        -- 拼接所有字段差异的消息，每个字段对应一个判断
        CONCAT(
            -- 处理measure_name差异，包含NULL值判断
            CASE 
                WHEN (s.measure_name IS NULL AND i.measure_name IS NOT NULL) 
                     OR (s.measure_name IS NOT NULL AND i.measure_name IS NULL) 
                     OR s.measure_name <> i.measure_name 
                THEN 'Measure Name Changed to <b>' + ISNULL(i.measure_name, 'NULL') + '</b>. ' 
                ELSE '' 
            END,
            -- 处理calculation_steps差异
            CASE 
                WHEN (s.calculation_steps IS NULL AND i.calculation_steps IS NOT NULL) 
                     OR (s.calculation_steps IS NOT NULL AND i.calculation_steps IS NULL) 
                     OR s.calculation_steps <> i.calculation_steps 
                THEN 'Calculation Steps changed to: <b>' + ISNULL(i.calculation_steps, 'NULL') + '</b>. ' 
                ELSE '' 
            END,
            -- 可以继续添加其他字段的判断，比如下面的数值型字段示例
            CASE 
                WHEN (s.target_value IS NULL AND i.target_value IS NOT NULL) 
                     OR (s.target_value IS NOT NULL AND i.target_value IS NULL) 
                     OR s.target_value <> i.target_value 
                THEN 'Target Value updated to: <b>' + CAST(ISNULL(i.target_value, 0) AS VARCHAR(MAX)) + '</b>. ' 
                ELSE '' 
            END
        ),
        s.measure_name AS oldname,
        i.measure_name AS newname,
        'Yes' AS milestone
    FROM inserted i
    -- 用LEFT JOIN关联上一条记录，避免第一条插入时没有匹配的情况
    LEFT JOIN Measures_slave s ON s.slave_PK = i.slave_PK - 1
    -- 只插入有差异的记录（或者去掉WHERE，插入所有记录，无差异时description为空）
    WHERE (
        s.measure_name <> i.measure_name 
        OR s.calculation_steps <> i.calculation_steps 
        OR s.target_value <> i.target_value
        -- 补充NULL差异的判断条件
        OR (s.measure_name IS NULL AND i.measure_name IS NOT NULL)
        OR (s.measure_name IS NOT NULL AND i.measure_name IS NULL)
    )
    -- 如果允许插入无差异的记录，删掉上面的WHERE子句即可
END

关键点说明： #

• 用LEFT JOIN替代多次子查询，既清晰又高效
• 每个CASE判断都包含了NULL值的处理，因为NULL <> 任何值不会返回true，必须单独判断
• 用CONCAT函数拼接多个差异消息，没有差异的字段会追加空字符串，不影响最终结果
• 如果字段是非字符串类型（比如数字、日期），要用CAST或者CONVERT转换成字符串才能拼接
• 可以通过WHERE子句控制只插入有差异的审计记录，或者保留所有记录（无差异时description为空）

方式二：游标逐行处理（适合复杂逻辑） #

如果你一定要用@message变量来逐行拼接消息，可以用游标处理inserted里的每条记录（因为inserted可能有多条记录，不能直接用单个变量处理所有记录）：

ALTER TRIGGER [dbo].[trg_Measure_Insert_Audit] 
ON [dbo].[Measures_slave] 
AFTER INSERT 
AS 
BEGIN
    SET NOCOUNT ON;

    -- 声明需要的变量
    DECLARE 
        @measure_PK INT,
        @old_measure_name VARCHAR(MAX),
        @new_measure_name VARCHAR(MAX),
        @old_calc_steps VARCHAR(MAX),
        @new_calc_steps VARCHAR(MAX),
        @old_target_value DECIMAL(18,2),
        @new_target_value DECIMAL(18,2),
        @message VARCHAR(MAX);

    -- 声明游标，获取插入记录和对应的上一条记录
    DECLARE insert_cursor CURSOR FOR
        SELECT 
            i.measure_PK,
            s.measure_name,
            i.measure_name,
            s.calculation_steps,
            i.calculation_steps,
            s.target_value,
            i.target_value
        FROM inserted i
        LEFT JOIN Measures_slave s ON s.slave_PK = i.slave_PK - 1
        WHERE s.slave_PK IS NOT NULL; -- 只处理有上一条记录的情况

    OPEN insert_cursor;
    FETCH NEXT FROM insert_cursor INTO @measure_PK, @old_measure_name, @new_measure_name, @old_calc_steps, @new_calc_steps, @old_target_value, @new_target_value;

    -- 循环处理每条记录
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @message = ''; -- 重置消息变量

        -- 判断measure_name差异
        IF (@old_measure_name <> @new_measure_name) 
            OR (@old_measure_name IS NULL AND @new_measure_name IS NOT NULL)
            OR (@old_measure_name IS NOT NULL AND @new_measure_name IS NULL)
        BEGIN
            SET @message = @message + 'Measure Name Changed to <b>' + ISNULL(@new_measure_name, 'NULL') + '</b>. ';
        END

        -- 判断calculation_steps差异
        IF (@old_calc_steps <> @new_calc_steps) 
            OR (@old_calc_steps IS NULL AND @new_calc_steps IS NOT NULL)
            OR (@old_calc_steps IS NOT NULL AND @new_calc_steps IS NULL)
        BEGIN
            SET @message = @message + 'Calculation Steps changed to: <b>' + ISNULL(@new_calc_steps, 'NULL') + '</b>. ';
        END

        -- 判断target_value差异（数字类型示例）
        IF (@old_target_value <> @new_target_value) 
            OR (@old_target_value IS NULL AND @new_target_value IS NOT NULL)
            OR (@old_target_value IS NOT NULL AND @new_target_value IS NULL)
        BEGIN
            SET @message = @message + 'Target Value updated to: <b>' + CAST(ISNULL(@new_target_value, 0) AS VARCHAR(MAX)) + '</b>. ';
        END

        -- 只有当有差异消息时才插入审计记录
        IF @message <> ''
        BEGIN
            INSERT INTO aud_Measures (measure_FK, [description], oldname, newname, milestone)
            VALUES (@measure_PK, @message, @old_measure_name, @new_measure_name, 'Yes');
        END

        FETCH NEXT FROM insert_cursor INTO @measure_PK, @old_measure_name, @new_measure_name, @old_calc_steps, @new_calc_steps, @old_target_value, @new_target_value;
    END

    -- 清理游标
    CLOSE insert_cursor;
    DEALLOCATE insert_cursor;
END

适用场景： #

• 当你需要更复杂的逐行逻辑（比如根据不同字段差异执行不同操作）时，游标会更灵活
• 注意游标性能不如集合操作，所以如果inserted每次只有少量记录，用游标没问题；如果批量插入，优先用方式一

最后提醒 #

• 一定要测试边界情况：比如第一条插入记录（没有上一条）、多个字段同时变化、字段值为NULL的情况
• 可以根据需求调整审计消息的格式，比如添加换行符（CHAR(13)+CHAR(10)）或者分隔符
• 如果字段长度很长，确保@message或者description字段的类型是VARCHAR(MAX)或者NVARCHAR(MAX)，避免截断

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