MySql 触发器，在插入时更新另一个表 [英] MySql trigger, update another table on insert

问题描述

我已经搜索并阅读了许多有关此问题的答案，但无法获得有关如何执行此操作的明确答案.

I've already, searched and read, many answers about this issue , but couldn't get a clear answer on how to do this.

我的查询如下:

DELIMITER //
CREATE TRIGGER `Stock_Update` AFTER INSERT ON `Store_db`.`Product_Supply` FOR EACH ROW
BEGIN
    UPDATE `Store_db`.`Stock` AS `ST`
    SET `ST`.`Stock_Quantity` = `ST`.`Stock_Quantity` + `Product_Supply`.`Supply_Quantity`
    WHERE `ST`.`Product_ID` = `Product_Supply`.`Product_ID`;
END//
DELIMITER ;

提前致谢.

附:一个更通用的答案也会很好，也可能对其他人有帮助

P.S. A More Generic Answer would be nice too and might be helpful for others as well

推荐答案

在给定表的触发器中，对该表字段的所有引用都必须以 NEW. 或 为前缀>OLD.，分别表示该字段在更改后或更改前的值.

From within a trigger on a given table, all references to fields of this table must be prefixed by either NEW. or OLD., which refers respectively to the value of this field after or before the change.

在您的情况下，您可能希望将新插入的数量添加到现有库存中:使用 NEW.Supply_Quantity(不要提及 Product_Supply，这已经暗示NEW 关键字).

In your case, you probably want to add the newly inserted quantity to your existing stock: use NEW.Supply_Quantity (do not mention Product_Supply, this is already implied by the NEW keyword).

同样，您当然希望在您的条件下使用 NEW.Product_ID.

Likewise, you certainly want to use NEW.Product_ID in your condition.

请注意 NEW 在删除触发器中不可用，例如在插入触发器中的 OLD.

Notice that NEW is not available in a trigger on deletion, like OLD in a trigger on insertion.

这篇关于MySql 触发器，在插入时更新另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！