My sql trigger, syntax error

Question

I want to update two columns after current row update by trigger. endtime and starttime are datetime type. duration is integer and it will represent num of seconds. Sequel Pro tell me "[ERROR in query 1] 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 5 Execution stopped!"

CREATE TRIGGER `end_time_update` AFTER UPDATE ON `mytable` 
FOR EACH ROW
BEGIN
UPDATE mytable
set endtime = now(), duration = TIMESTAMPDIFF(SECOND, starttime, endtime)
where id = new.id;
END;

juergen d · Accepted Answer · 2015-10-16 07:51:49Z

1

You need to change the delimiter. Otherwise the DB will terminate the definition of the trigger at the first ;

delimiter |
CREATE TRIGGER `end_time_update` BEFORE UPDATE ON `mytable` 
FOR EACH ROW
BEGIN
   if NEW.some_col <> OLD.some_col
   then
      set NEW.endtime = now();
      set NEW.duration = TIMESTAMPDIFF(SECOND, NEW.starttime, NEW.endtime);
   end if;
END
|
delimiter ;

You can't put an update on the same table in the trigger. That would create an endless loop. But you can use NEWto refer to the current record to modify it.

edited Oct 16, 2015 at 7:51

answered Oct 13, 2015 at 9:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

5 Comments

michael Over a year ago

After creation of trigger any update fails: Can't update table 'mytable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

juergen d Over a year ago

You can't put an update on the same table in the trigger. That would create an endless loop. But you can use NEWto refer to the current record to modify it. I updated the answer

michael Over a year ago

juergen, I've used your update answer but now sequel pro tell me: Updating of NEW row is not allowed in after trigger

juergen d Over a year ago

Then use BEFORE UPDATE ON mytable instead of AFTER UPDATE ON mytable

michael Over a year ago

Hi, if I want to update only when a specified column is changing, how to?

Mureinik · Accepted Answer · 2015-10-13 09:15:56Z

1

You should set your delimiter so that the semi-colons inside the trigger won't be interpreted as the end of the trigger:

-- Set the delimiter
DELIMITER $$

CREATE TRIGGER `end_time_update` AFTER UPDATE ON `mytable` 
FOR EACH ROW
BEGIN
UPDATE mytable
set endtime = now(), duration = TIMESTAMPDIFF(SECOND, starttime, endtime)
where id = new.id;
END;
$$

-- Reset the delimiter
DELIMITER ;

answered Oct 13, 2015 at 9:15

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Collectives™ on Stack Overflow

My sql trigger, syntax error

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related