Mysql Trigger - Update table when insert another table

Question

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!

And what is your problem? Do you get an error? If yes, please add that message and add, when you get the message (e.g when you insert or when you try to create the trigger). Or isn't it working/does it something unexpected? Then describe that. — Solarflare
– Solarflare, Commented Jul 29, 2019 at 7:28

Mickaël Leger · Accepted Answer · 2019-07-29 07:37:57Z

1

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 ?

edited Jul 29, 2019 at 7:37

answered Jul 29, 2019 at 7:24

Mickaël Leger

3,4482 gold badges21 silver badges37 bronze badges

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

1 Comment

Ajax Over a year ago

Great. That's what I need. Thank you very much!

Collectives™ on Stack Overflow

Mysql Trigger - Update table when insert another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related