CODEWITHSUNDEEP
mysqlsqltriggerssyntax-error
michael
michael

Reputation: 145

My sql trigger, syntax error

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;

Upvotes: 0

Views: 224

Answers (2)

juergen d
juergen d

Reputation: 204756

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.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

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 ;

Upvotes: 1

Related Questions