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.一个更通用的答案也很好，可能对其他人也有帮助

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屋！