MySQL Trigger Syntax Error

Question

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?

Stefan Falk · Accepted Answer · 2016-08-02 16:17:51Z

5

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 ;

edited Aug 2, 2016 at 16:17

Stefan Falk

25.8k62 gold badges227 silver badges422 bronze badges

answered Feb 10, 2012 at 11:23

Timur

6,7381 gold badge30 silver badges37 bronze badges

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

3 Comments

Jan Jongboom Over a year ago

This works, but I have no clue why the DELIMITER stuff is necessary.

Timur Over a year ago

Because BEGIN ... END is a "body" of trigger. So, it is must be a part of current query. ; is a delimeter by default, so when MySQL reaches it, it thinks that this is the end of query and cannot parse it. Also, you must put delimeters in "body" of trigger because it would be invalid in another way. So, to meet this requirements, we need to use two different delimeters in "body" of trigger and in query for itself. Sorry, my english is too bad to describe this not obvious thing better...

Timur Over a year ago

This better described here: dev.mysql.com/doc/refman/5.5/en/begin-end.html (third paragraph, starts with "Use of multiple statements requires that a client is able")

juergen d · Accepted Answer · 2012-02-10 11:18:56Z

0

You are missing a ; before your END keyword:

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;

answered Feb 10, 2012 at 11:18

juergen d

205k40 gold badges305 silver badges377 bronze badges

1 Comment

Jan Jongboom Over a year ago

Doesn't work, gives errors on both the last and the second last line now.

Collectives™ on Stack Overflow

MySQL Trigger Syntax Error

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related