如何正确使用触发器更新值？完善Products表销量迁移触发器

你的现有触发器存在几个关键问题，导致无法实现预期功能：

• 变量@x未初始化，默认值为NULL，循环条件@x < @@ROWCOUNT永远不成立，循环体不会执行
• 逻辑颠倒：代码试图将QtySold_Yesterday的值赋给QtySold_Day，与“将当日销量迁移至昨日列”的需求相反
• 错误使用@@ROWCOUNT：它表示受影响的行数，并非ProductID的取值
• 逐行循环的方式不符合SQL的集合操作特性，效率低下

以下是两种可行的完善方案，根据你的业务场景选择：

方案1：AFTER UPDATE触发器（推荐） #

在原更新操作完成后，将更新前的当日销量同步至昨日列：

CREATE OR ALTER TRIGGER UpdateQuantity
ON Products
AFTER UPDATE
AS
SET NOCOUNT ON;

-- 仅当QtySold_Day列被更新时执行操作
IF UPDATE(QtySold_Day)
BEGIN
    UPDATE p
    SET p.QtySold_Yesterday = d.QtySold_Day
    FROM Products p
    INNER JOIN deleted d ON p.ProductID = d.ProductID
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ProductID = p.ProductID);
END

关键点说明： #

• UPDATE(QtySold_Day)：判断是否只有QtySold_Day列被更新，避免无意义的操作
• deleted表：存储更新前的旧数据，这里取原QtySold_Day的值
• inserted表：存储更新后的新数据，用于定位被修改的行
• 集合操作：一次性处理所有被更新的行，效率远高于逐行循环

方案2：INSTEAD OF UPDATE触发器 #

替代原更新操作，先完成销量迁移，再设置新的当日销量：

CREATE OR ALTER TRIGGER UpdateQuantity
ON Products
INSTEAD OF UPDATE
AS
SET NOCOUNT ON;

IF UPDATE(QtySold_Day)
BEGIN
    -- 第一步：将当前当日销量迁移至昨日列
    UPDATE p
    SET p.QtySold_Yesterday = p.QtySold_Day
    FROM Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;

    -- 第二步：更新当日销量为新值
    UPDATE p
    SET p.QtySold_Day = i.QtySold_Day
    FROM Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END
ELSE
BEGIN
    -- 若更新的不是QtySold_Day列，执行原更新逻辑（需列出其他可更新列）
    UPDATE p
    SET 
        ProductName = i.ProductName,
        Price = i.Price
        -- 添加其他需要支持更新的列
    FROM Products p
    INNER JOIN inserted i ON p.ProductID = i.ProductID;
END

适用场景： #

需要严格控制更新顺序（先迁移再更新），或原更新操作需要额外自定义逻辑时使用。注意需手动处理非QtySold_Day列的更新需求。

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