MySQL trigger #1064 syntax error

Question

I have a database already in use on a server (MySQL Community Server 5.5.40-cll) & I'm trying to add a trigger to it but I keep getting the below error.

#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 3

I'm trying to set a trigger to make sure that end_date is not before start_date, here's what I'm currently using.

CREATE TRIGGER `Meets insert` BEFORE INSERT ON `meets`
  FOR EACH ROW 
    IF DATEDIFF(NEW.end_date, NEW.start_date) < 0 THEN
      signal sqlstate '45000' set message_text = 'start_date must be before end_date';
    END IF;

I've had a look in the MySQL documentation for 5.5 and it seems like everything I'm doing is supported by this version and I'm using it correctly.

I'm sure the if statement is correct as I asked another similar question earlier here on Stack Overflow about a separate problem I was having which worked fine. Although that was on a local development database where as this is on a server.

If anyone can spot what I'm doing wrong or has an idea of what to check it will be appreciated.

Thought I'd point out the server I'm making this change on isn't a production one.

Community · Accepted Answer · 2017-05-23 12:15:11Z

1

It appears to only be a lack of a DELIMTER wrapper. Try

delimiter $$
CREATE TRIGGER `Meets insert` BEFORE INSERT ON `meets`
  FOR EACH ROW 
    IF DATEDIFF(NEW.end_date, NEW.start_date) < 0 THEN
      signal sqlstate '45000' set message_text = 'start_date must be before end_date';
    END IF;
$$
delimiter ;

which gets past the Error 1064

See the bottom of This Post for DELIMITER verbiage.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Dec 4, 2015 at 8:25

Drew

25k10 gold badges47 silver badges81 bronze badges

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

2 Comments

Neil Nand Over a year ago

That seems to have solved, I've not come across that before.

Drew Over a year ago

it's ridiculous and people waste hours on it. From the server consuming part of it though, it needs to know where one thing begins and ends (the outer envelope of the trigger, stored proc, event etc), and the commands within it

Collectives™ on Stack Overflow

MySQL trigger #1064 syntax error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related