MySQL Delcare causing error

Question

I am trying to create a trigger that marks items as deleted when they are inserted into the database.

Sadly I can't get my DECLARE to stop erroring, I have looked at the DECLARE docs and also at a few examples but I must be missing something.

The query I have so far is:

CREATE TRIGGER set_deleted BEFORE INSERT ON customercontact
FOR EACH ROW
BEGIN
DECLARE numrow INT; /* line 4 */

        SELECT COUNT(*)
        INTO numrow
        FROM orders
        WHERE NEW.order_id = 1;

        if numrow >= 1 THEN
            SET new.deleted = 1;
        END IF;
END

The error message is showing:

#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 '' at line 4

Thanks for your help and preventing me from defenestrating myself!

score 2 · Answer 1 · edited Oct 16 '13 at 09:24

Try this:

DELIMITER $$

CREATE TRIGGER set_deleted BEFORE INSERT ON customercontact
FOR EACH ROW
BEGIN
DECLARE numrow INT; /* line 4 */

        SELECT COUNT(*)
        INTO numrow
        FROM orders
        WHERE NEW.order_id = 1;

        if numrow >= 1 THEN
            SET new.deleted = 1;
        END IF;
END$$

DELIMITER ;

You need to change the delimiter when you create TRIGGER or STORED PROCEDURE.

By default, MySQL itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause MySQL to pass the entire stored program definition to the server. Otherwise, MySQL breaks CREATE TRIGGER, before it reaches the END statement (on the first semicolon, which, in your case, is DECLARE statement).

You can see the documentation for more details: http://dev.mysql.com/doc/refman/5.5/en/stored-programs-defining.html

Thanks for explaining that, it's working now! Hoorah! No defenestration for meeee — Luke, Oct 16 '13 at 09:25

MySQL Delcare causing error

1 Answers1