MySQL Syntax Error on Trigger

Question

I cannot get this trigger code to compile on MySQL 5.3:

CREATE TRIGGER crmenq_bur 
BEFORE UPDATE 
ON crmenquiries
FOR EACH ROW
BEGIN
   IF (    NEW.feedback = OLD.feedback 
       AND NEW.notes = OLD.notes
       AND NEW.estatus = OLD.estatus )
   THEN
      SET NEW.update_type = 'NN';
   ELSE
      SET NEW.update_type = 'SEN';
      SET new.last_update2 = now();
   END IF;
   CASE 
      WHEN NEW.estatus != OLD.estatus AND NEW.estatus = 'FP3' THEN
         SET NEW.uid_follow_up = NEW.uid_last_update_by;
         SET NEW.follow_up_date = DATE_ADD ( date(), INTERVAL 3 MONTH );
      WHEN NEW.estatus != OLD.estatus AND NEW.estatus = 'FP6' THEN
         SET NEW.uid_follow_up = NEW.uid_last_update_by;
         SET NEW.follow_up_date = DATE_ADD ( date(), INTERVAL 6 MONTH );
      WHEN NEW.estatus != OLD.estatus AND NEW.estatus = 'FP9' THEN
         SET NEW.uid_follow_up = NEW.uid_last_update_by;
         SET NEW.follow_up_date = DATE_ADD ( date(), INTERVAL 9 MONTH );
      WHEN NEW.estatus != OLD.estatus AND NEW.estatus = 'FP12' THEN
         SET NEW.uid_follow_up = NEW.uid_last_update_by;
         SET NEW.follow_up_date = DATE_ADD ( date(), INTERVAL 12 MONTH );
      WHEN NEW.estatus NOT IN ( 'FP3', 'FP6', 'FP9', 'FP12' ) THEN
         SET NEW.uid_follow_up = NULL;
         SET NEW.follow_up_date = NULL;
   END CASE;
END;

When I run this I get the following error:

[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 '), INTERVAL 3 MONTH ); WHEN NEW.estatus != OLD.estatus AND NEW.estatus = ' at line 18]

Initially I had nested IF but changed to CASE to see if that made any difference. The current form is not how I had it originally (nested IF to avoid repeating the same condition) but with each unsuccessful attempt the code was changed but nothing I have tried has worked.

The column/tables names are correct.

Ravinder Reddy · Accepted Answer · 2014-07-01 09:32:07Z

1

Error thrown is due to call on MySQL date() function. It was wrongly used.

I am not sure if your intention was to use current date.
If yes, you can use any of the following suggestions on all date() usages in your code.

curdate() -- Return the current date
current_date() -- Synonyms for CURDATE()
current_date -- Synonyms for CURDATE()

Example:

mysql> select curdate(), current_date(), current_date;
+------------+----------------+--------------+
| curdate()  | current_date() | current_date |
+------------+----------------+--------------+
| 2014-07-01 | 2014-07-01     | 2014-07-01   |
+------------+----------------+--------------+

You can also use date() function but it takes an input parameter of type date, datetime or timestamp.

Example:

mysql> select date( sysdate() ), date( now() ), date( curtime() ), date( curdate() );
+-------------------+---------------+-------------------+-------------------+
| date( sysdate() ) | date( now() ) | date( curtime() ) | date( curdate() ) |
+-------------------+---------------+-------------------+-------------------+
| 2014-07-01        | 2014-07-01    | 2014-07-01        | 2014-07-01        |
+-------------------+---------------+-------------------+-------------------+

Refer to documentation:

MySQL: Date and Time Functions

answered Jul 1, 2014 at 9:32

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

2 Comments

TenG Over a year ago

Thanks, I tried this but got the same, i.e. changed: SET NEW.follow_up_date = DATE_ADD ( date(), INTERVAL 12 MONTH ); to: SET NEW.follow_up_date = DATE_ADD ( curdate(), INTERVAL 12 MONTH ); But got the same, however I was then able to try a few other things out and eventually got this working: SET NEW.follow_up_date = ( date(now()) + INTERVAL 12 MONTH ); So thanks for the help.

Ravinder Reddy Over a year ago

Repeated error was not due to use of curdate(). I am not sure if you have changed all date() occurrences to curdate() in the code. If yes, it should have worked. And, you can also use curdate() + interval .. instead of date(now()) + interval ..

user1864610 · Accepted Answer · 2014-07-01 08:12:27Z

0

Change the delimiter with something like Delimiter $$ before you start, and end your trigger with the new delimiter. Change it back when you're done, like this:

Delimiter $$
CREATE TRIGGER crmenq_bur 
BEFORE UPDATE 
ON crmenquiries
FOR EACH ROW
BEGIN
 /* Body of code here */
END $$
Delimiter ;

answered Jul 1, 2014 at 8:12

user1864610

1 Comment

Ravinder Reddy Over a year ago

This is not correct. If the error was on delimiter, exception would have been thrown on line SET NEW.update_type = 'NN';

Collectives™ on Stack Overflow

MySQL Syntax Error on Trigger

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related