Cannot declare variable in MySQL trigger

Question

I am having difficulty creating the following trigger in MySQL:

CREATE TRIGGER TRG_COMPLETE_REMINDER
AFTER UPDATE ON reminders
FOR EACH ROW
BEGIN
    DECLARE
        new_date DATE;
    IF (NEW.complete = 1 AND recurrence <> 'NONE') THEN
        CASE recurrence
            WHEN '1 WEEK' THEN
                SELECT INTO new_date NEW.date + INTERVAL 7 DAY;
            WHEN '1 MONTH' THEN
                SELECT INTO new_date  NEW.date + INTERVAL 1 MONTH;
            WHEN '3 MONTH' THEN
                SELECT INTO new_date  NEW.date + INTERVAL 3 MONTH;
        END CASE;

        INSERT INTO reminders (description, date, userID, complete, recurrence)
        VALUES (NEW.description, new_date, NEW.userID, 0, NEW.recurrence);
    END IF;
END;

The issue seems to be occurring where I attempt to declare new_date. MySQL returns the following error message:

#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 'INTO new_date NEW.date + INTERVAL 7 DAY' at line 8

I would like this trigger to create a new reminder record when the complete attribute changes to 1. The new record should have a date greater than the original record, depending on the value stored in recurrence.

Your select is the wrong way round it should be SELECT value INTO variable - I don't know if that solves everything. You could also do SET new_date = NEW.date + INTERVAL 7 DAY; — PaulF
– PaulF, Commented Jun 6, 2016 at 15:54
@PaulF You are right, but this does not fix the DECLARE issue. — Matthew Fisher
– Matthew Fisher, Commented Jun 6, 2016 at 15:57
What error do you get on the DECLARE - the error you reported was related to the incorrect SELECT INTO syntax only. — PaulF
– PaulF, Commented Jun 6, 2016 at 16:00
@ÁlvaroGonzález there were two issues in the problem though, so not completely duplicate - originally OP reported the SELECT INTO syntax error before the edit to show the error at line #6. — PaulF
– PaulF, Commented Jun 6, 2016 at 16:10

Matthew Fisher · Accepted Answer · 2016-06-06 16:17:07Z

1

Two corrections fixed the issue:

Correcting the order of clauses in the SELECT statement (i.e. moving INTO new_date after SELECT new_date + INTERVAL 7 DAY)
Adding DELIMITER // before creating the trigger

answered Jun 6, 2016 at 16:17

Matthew Fisher

279 bronze badges

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

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2016-06-06 16:45:04Z

1

Rather than use a variable you can use a CASE expression right in the INSERT statement:

CREATE TRIGGER TRG_COMPLETE_REMINDER
  AFTER UPDATE ON reminders
  FOR EACH ROW
BEGIN
  IF (NEW.complete = 1 AND NEW.recurrence <> 'NONE') THEN
    INSERT INTO reminders
     (description,
      date,
      userID,     complete, recurrence)
    VALUES
     (NEW.description, 
      CASE NEW.recurrence
        WHEN '1 WEEK' THEN
          NEW.date + INTERVAL 7 DAY
        WHEN '1 MONTH' THEN
          NEW.date + INTERVAL 1 MONTH
        WHEN '3 MONTH' THEN
          NEW.date + INTERVAL 3 MONTH
        ELSE
          NULL
      END,
      NEW.userID, 0,        NEW.recurrence);
  END IF;
END;

Best of luck.

answered Jun 6, 2016 at 16:45

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

Cannot declare variable in MySQL trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related