解决触发器中多部分标识符无法绑定错误及重复插入问题

错误原因 #

• 别名i仅在定义它的SELECT语句范围内有效，IF NOT EXISTS子查询不属于该作用域，因此无法识别i.Inventoryid。
• 原代码用单个变量存储新旧值，仅支持单行数据变更，批量插入/更新时会丢失数据。
• 重复判断逻辑错误，未针对每一行变更做独立校验。

修正后的触发器代码 #

CREATE TRIGGER trg_UpdateOrderMultiple
ON tbl_InventoryPolicy
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- 阻止返回受影响行数的消息，避免干扰调用方

    -- 直接基于inserted和deleted的关联进行操作，支持多行变更
    INSERT INTO tbl_InventoryPolicyChanges 
                (InventoryID, OldOrderMultiple, NewOrderMultiple, [Timestamp])
    SELECT 
        i.InventoryID,
        d.ordermultiple AS OldOrderMultiple, -- INSERT时d为NULL，自动填充旧值为NULL
        i.ordermultiple AS NewOrderMultiple,
        GETDATE() AS [Timestamp]
    FROM inserted i
    LEFT JOIN deleted d ON i.Inventoryid = d.Inventoryid -- LEFT JOIN兼容INSERT操作（deleted无数据）
    WHERE 
        -- 仅当新旧值不同时才插入（避免无意义的变更记录）
        ISNULL(d.ordermultiple, -1) <> ISNULL(i.ordermultiple, -1)
        AND NOT EXISTS (
            -- 校验当前变更是否已存在于历史表中
            SELECT 1
            FROM tbl_InventoryPolicyChanges c
            WHERE c.Inventoryid = i.Inventoryid
              AND c.OldOrderMultiple = d.ordermultiple
              AND c.NewOrderMultiple = i.ordermultiple
        );
END

关键优化点 #

• 作用域问题解决：在INSERT...SELECT语句内部直接使用i和d的别名，确保作用域有效。
• 多行支持：直接从inserted和deleted取数，避免单变量丢失批量数据。
• 重复插入规避：通过NOT EXISTS针对每一行变更校验历史表，确保相同的变更不会重复插入。
• 兼容INSERT操作：用LEFT JOIN关联deleted，INSERT时旧值自动为NULL，符合业务逻辑。
• 过滤无意义变更：加入ISNULL(d.ordermultiple, -1) <> ISNULL(i.ordermultiple, -1)，避免字段值未变化时插入冗余记录。

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