MySql trigger updating another table insert

Question

I have already searched and read many answers about this problem, but I could not get a clear answer on how to do this.

My request is as follows:

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 ; 

Thanks in advance.

PS A more general answer would be good too and may be useful to others as well

Answer 1

Inside a trigger in a given table, all links to the fields of this table must be prefix or using NEW. or OLD. , which refers accordingly to the value of this field after or before the change.

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

Similarly, you certainly want to use NEW.Product_ID in your state.

Please note that NEW not available in the trigger when deleting, for example OLD in the trigger when inserting.