CODEWITHSUNDEEP
mysqltriggers
Ajax
Ajax

Reputation: 87

Mysql Trigger - Update table when insert another table

I have three tables:

Import Table:

import_id  | import_date
-------------------------
   1       | 2019/07/29
   2       | 2019/07/28

ImportItem Table:

import_id    |  product_id |  quantity
-------------------------------------
    1        |     1       |   50
    1        |     2       |   60
    1        |     3       |   20

Product Table:

product_id   |  quantity   
---------------------------
    1        |     10       
    2        |     5       
    3        |     15

I want to create a trigger update quantity in product table when insert ImportItem table.

Any help much appreciated! Thanks very much!

Upvotes: 0

Views: 275

Answers (1)

Mickaël Leger
Mickaël Leger

Reputation: 3440

If you create a trigger in your table import_item that will update your table product after each import_item INSERT you can do this :

CREATE TRIGGER trigger_name
AFTER INSERT
ON import_item FOR EACH ROW
BEGIN
    UPDATE product SET quantity = NEW.quantity WHERE product_id = NEW.product_id;
END

Here is some documentation about the trigger key words NEW and OLD : documentation

When you are in a BEFORE/AFTER INSERT trigger, you can use NEW.field to get the value of the new field you just inserted.

Now if you are in a BEFORE/AFTER UPDATE trigger, you can use NEW.field to get the value of the field AFTER the update and OLD.value to get the value of the field BEFORE the update.

And the last, if you are in a BEFORE/AFTER DELETE trigger , you can use OLD.field to get the value fo the field before the delete.

Is it what you are looking for ?

Upvotes: 1

Related Questions