MySql trigger, update another table on insert

Question

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

My query is the following:

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.

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

Answer 1

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.

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).

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

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