MySQL Before insert trigger syntax error?

Question

I'm trying to create a simple Before Insert MySQL Trigger, to check if any duplicates exists in the table. But I get syntax errors in the lines with the (*). What's wrong ?

       delimiter ;

(*)    CREATE TRIGGER `BookLanguages_BeforeInsertTrigger`
       BEFORE INSERT ON `BookLanguages`
       FOR EACH ROW
       BEGIN
            IF (exists(select * from Languages bl where bl.BookID = new.BookID and bl.LanguageID = new.LanguageID)) THEN
               SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred';
            END IF;
(*)    END;

I'm using the Community server v5.5.21

Jeremy Smyth · Accepted Answer · 2012-11-08 10:14:15Z

3

Use a different delimiter, for example:

DELIMITER //

...and then use // in your END // line.

The reason being that the semicolon in your second-last line is sending everything up to that point back to the server, so the server doesn't see your final END; and creates the trigger without seeing that last line. That's a syntax error, in its view.

The purpose of the DELIMITER command in this context is to distinguish between the end of each statement in the trigger (for the server), and the end of the trigger (for the client to send it to the server). While they're different, you can still use semicolons in the body of the trigger without the client thinking you've terminated the statement.

answered Nov 8, 2012 at 10:14

Jeremy Smyth

23.6k2 gold badges57 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

MySQL Before insert trigger syntax error?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related