Search code examples
mysqltriggersinnodb

MySQL Trigger Syntax Error


Running on MySQL 5.5.9 with InnoDB.

I created the following trigger:

CREATE TRIGGER TRIGGER_Products_Insert
AFTER INSERT ON Products

FOR EACH ROW
BEGIN
UPDATE Products
SET current = 0
WHERE   id = new.id
    AND current = 1
    AND autonumber <> new.autonumber
END;

MySQLWorkbench shows a syntax error on the last line, and executing this throws the following error

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 11

Where is my error?


Solution

  • http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

    DELIMITER |
    
    CREATE TRIGGER TRIGGER_Products_Insert AFTER INSERT ON Products
        FOR EACH ROW BEGIN
            UPDATE Products
            SET current = 0
            WHERE   id = new.id
                AND current = 1
                AND autonumber <> new.autonumber;
        END;
    |
    
    DELIMITER ;