Can I not use a Trigger correctly in MYSQL?

Asked

Viewed 30 times

0

delimiter //

CREATE TRIGGER inventory_update_purchases AFTER INSERT ON purchases
FOR EACH ROW 
BEGIN
    UPDATE products
        SET products.inventory = products.inventory + NEW.inventory_bought
        WHERE products.product_id = NEW.product_id
END;//

delimiter ;

What I want to do is change the inventory when a shipment of suppliers arrives!

I’ve seen solutions online and they literally do what I have here.

The mistake you give me is about the function of END:

(Error Code: 1064. You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'END' at line 7)

This sounds repetitive but I’ve tried almost everything (just need to change the mysql version, the only thing I haven’t tried yet).

mysql sql trigger

  • Isn’t one missing ; to complete the command UPDATE?

  • not because it is not the update command (since we are inside a Trigger --> I tried to do this and pointed out as an error)...I think it may be the END delimiter but it gives me nothing...

  • I have to agree with @anonimo. According to the documentation ; is necessary after each instruction, regardless of being within a trigger or not. If we look at the documentation (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html) they present numerous examples of how to implement and in all of them the ; is used.

  • Sorry! I just thought of ; in the end.. after putting in the Where, it worked out fully! Thank you very much!

1 answer

0


Reinforcing my comment.

CREATE TRIGGER inventory_update_purchases AFTER INSERT ON purchases
FOR EACH ROW 
BEGIN
    UPDATE products
        SET products.inventory = products.inventory + NEW.inventory_bought
        WHERE products.product_id = NEW.product_id; END;

Fiddler: http://sqlfiddle.com/#! 9/9a37486

Browser other questions tagged mysql sql trigger

You are not signed in. Login or sign up in order to post.