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

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.

Collectives™ on Stack Overflow

MySql trigger, update another table on insert

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related