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

A quick description of the error you are getting would have been helpful. — RandomSeed
– RandomSeed, Commented Jun 11, 2013 at 15:02
I was getting that it couldnt find the columns that you later told me to replace with NEW.ColumnName — Mitsosp
– Mitsosp, Commented Jun 11, 2013 at 16:18

RandomSeed · Accepted Answer · 2013-06-11 15:01:58Z

4

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.

answered Jun 11, 2013 at 15:01

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql trigger, update another table on insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related