mysql - 如何在 MySQL 触发器中访问 "INSERTED"值？

Question

如何在触发器中访问 INSERTED 表的值？

例如:

INSERT INTO sales (sku, qty) 
VALUES
(1001, 5), //I need these values in the trigger
(1002, 1)
...

这是我的触发器:

DELIMITER $$

CREATE TRIGGER after_sales_insert 
AFTER INSERT ON sales 
FOR EACH ROW BEGIN

    UPDATE products
    SET NEW.qty = OLD.qty - INSERTED.qty
    WHERE sku = INSERTED.sku;

END;
$$
DELIMITER;

请注意，sales.sku 是 products 表的外键。

SQL Server 有 INSERTED 关键字，它似乎不适用于 MySQL。

回答:

NEW.qty 引用设置了触发器的表上的数量，不是正在更新的表。

CREATE TRIGGER after_sales_insert 
AFTER INSERT ON sales 
FOR EACH ROW BEGIN

    UPDATE products
    SET qty = qty - NEW.qty
    WHERE sku = NEW.sku;

END;

Answer 1

给定:

INSERT INTO sales (sku, qty) 
VALUES
(1001, 5), //I need these values in the trigger
(1002, 1)
...

我相信你想要这个:

CREATE TRIGGER after_sales_insert AFTER INSERT ON sales 
FOR EACH ROW BEGIN
    UPDATE products
    SET qty = qty - NEW.qty
    WHERE sku = NEW.sku;
END;

引用资料:

• MySQL 8: 24.3.1 Trigger Syntax and Examples

• How to program a MySQL trigger to insert row into another table?

• How does "for each row" work in triggers in mysql?