dimanche 22 mars 2020

Why do I get a syntax error when trying to put an if statement in a trigger?

I need to insert different data in db using a trigger depending on the previously done update on a table, though i keep on getting a syntax error for some reason. I checked out a lot of similar questions to mine but still my code seems fine.

trigger:

DELIMITER $$

CREATE TRIGGER update_history
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
IF OLD.amount - NEW.amount > OLD.amount THEN
    INSERT INTO product_history (amount_of_new_products, product_id, user_id, updated_on, type_of_change)
    VALUES ((NEW.amount - OLD.amount), NEW.id, NEW.user_id, now(), 'order');
ELSE
    INSERT INTO product_history (amount_of_new_products, product_id, user_id, updated_on, type_of_change)
    VALUES ((NEW.amount - OLD.amount), NEW.id, NEW.user_id, now(), 'update');
END$$

DELIMITER ;

error:

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 '' at line 11

I am aware that this might be something quite simple though I am just not able to find the mistake.

Aucun commentaire:

Enregistrer un commentaire