代码之家 › 专栏 › 技术社区 › Lord Elrond Mureinik

如何访问MySQL触发器中的“插入”值?

sql mysql

Lord Elrond Mureinik · 技术社区 · 7 年前

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具有关键字,它似乎不适用于MySQL。

答复:

NEW.qty 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;

1 回复 | 直到 7 年前

paulsm4 7 年前

鉴于:

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;

参考资料: