error while creating trigger on update operation in mysql5?

Question

I am creating a trigger on update operation. However, while creating trigger in MySQL5 it is giving an error.

Trigger:

CREATE TRIGGER `test`.`Employee_Trigger_Update`
     AFTER UPDATE ON `test`.`employee`
     FOR EACH ROW
     BEGIN
     INSERT into `test`.`Employee_log`
     (id,description)
     VALUES 
     (old.id, CONCAT('Id with '
                       , old.id
                       ,' is modified from '
                       , OLD.start_date
                       , ' to '
                       , NEW.start_date)    );

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 '' at line 8 (0 ms taken)

If some one could please help it would be greatly appreciated

I just answered a question with the same problem 2 min ago. See stackoverflow.com/questions/5013246/… — The Scrum Meister
– The Scrum Meister, Commented Feb 16, 2011 at 6:54

RolandoMySQLDBA · Accepted Answer · 2013-04-22 20:53:39Z

2

You need to use a different delimiter other than semicolon

DELIMITER $$

CREATE TRIGGER `test`.`Employee_Trigger_Update`
     AFTER UPDATE ON `test`.`employee`
     FOR EACH ROW
     BEGIN
     INSERT into `test`.`Employee_log`
     (id,description)
     VALUES 
     (old.id, CONCAT('Id with ',old.id,' is modified from ',OLD.start_date, ' to ', NEW.start_date));
END $$

DELIMITER ;

edited Apr 22, 2013 at 20:53

answered Feb 16, 2011 at 6:52

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

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

1 Comment

Yugal Over a year ago

now i am getting 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 $$ DELIMITER' at line 1 (0 ms taken)

Haim Evgi · Accepted Answer · 2011-02-16 06:47:51Z

1

one thing i see , you dont have - END; in the end of trigger declare , see examples and documentation here

answered Feb 16, 2011 at 6:47

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

1 Comment

Yugal Over a year ago

i have used "END ;" at the end of query but now it is giving 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 1 (0 ms taken)

Collectives™ on Stack Overflow

error while creating trigger on update operation in mysql5?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related