Syntax error on MYSQL Trigger

Question

I'm getting an error on my MYSQL trigger. I've used various form of syntax that I've found both on here and elsewhere on the internet but nothing seems to work. Here are 3 examples of syntax I've used:

>         CREATE TRIGGER LOG_UPDATE BEFORE UPDATE ON WORKLOG
>     FOR EACH ROW BEGIN
>       INSERT INTO WORKLOG_BACKUP VALUES(NULL, CURRENT_TIMESTAMP, NEW.LOGNO, NEW.JOBNO, NEW.EMPLOYEENO, NEW.WORKDATE, NEW.WORKTIME,
> 'UPDATE');    
>     END;
>     
>     CREATE TRIGGER LOG_UPDATE BEFORE UPDATE ON WORKLOG
>     FOR EACH ROW BEGIN
>       UPDATE WORKLOG_BACKUP SET BACKUPSTAMP = CURRENT_TIMESTAMP,
>       UPDATE WORKLOG_BACKUP SET LOGNO = NEW.LOGNO,
>       UPDATE WORKLOG_BACKUP SET JOBNO = NEW.JOBNO,
>       UPDATE WORKLOG_BACKUP SET EMPLOYEENO = NEW.EMPLOYEENO,
>       UPDATE WORKLOG_BACKUP SET WORKDATE = NEW.WORKDATE,
>       UPDATE WORKLOG_BACKUP SET WORKTIME = NEW.WORKTIME,
>       UPDATE WORKLOG_BACKUP SET CAUSE = 'UPDATE'; 
>     END;
>     
>     CREATE TRIGGER LOG_UPDATE BEFORE UPDATE ON WORKLOG
>     FOR EACH ROW BEGIN
>       INSERT INTO WORKLOG_BACKUP VALUES(
>       SET BACKUPSTAMP = CURRENT_TIMESTAMP,
>       SET LOGNO = NEW.LOGNO,
>       SET JOBNO = NEW.JOBNO,
>       SET EMPLOYEENO = NEW.EMPLOYEENO,
>       SET WORKDATE = NEW.WORKDATE,
>       SET WORKTIME = NEW.WORKTIME,
>       SET CAUSE = 'UPDATE');  
>     END;

I'd appriciate any help anyone can provide. I'm using phpmyadmin.

eggyal · Accepted Answer · 2012-12-01 12:13:35Z

4

In order for your MySQL client not to interpret the ; that terminates the INSERT statement as the end of the CREATE TRIGGER statement, you must inform it that you wish to use some other statement delimiter.

In the mysql command-line client, you can do this with the DELIMITER command. For example, to change your statement delimiter to a double-semicolon:

DELIMITER ;;

Then you can do:

CREATE TRIGGER LOG_UPDATE BEFORE UPDATE ON WORKLOG FOR EACH ROW BEGIN
  INSERT INTO WORKLOG_BACKUP VALUES (
    NULL,
    CURRENT_TIMESTAMP,
    NEW.LOGNO,
    NEW.JOBNO,
    NEW.EMPLOYEENO,
    NEW.WORKDATE,
    NEW.WORKTIME,
    'UPDATE'
  );
END;;

However, because in this case your trigger only contains one statement, you don't need to use a BEGIN ... END compound statement block and could therefore avoid changing delimiters altogether:

CREATE TRIGGER LOG_UPDATE BEFORE UPDATE ON WORKLOG FOR EACH ROW
  INSERT INTO WORKLOG_BACKUP VALUES (
    NULL,
    CURRENT_TIMESTAMP,
    NEW.LOGNO,
    NEW.JOBNO,
    NEW.EMPLOYEENO,
    NEW.WORKDATE,
    NEW.WORKTIME,
    'UPDATE'
  )
;

answered Dec 1, 2012 at 12:13

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

2 Comments

JamesT Over a year ago

Thank you! That worked, however now I have a new error. It's saying #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

eggyal Over a year ago

Well, that error is pretty clear isn't it? It's even clearly documented: "In particular, you cannot have two triggers for a table that have the same activation time and activation event. For example, you cannot define two BEFORE INSERT triggers or two AFTER UPDATE triggers for a table. This should rarely be a significant limitation, because it is possible to define a trigger that executes multiple statements by using the BEGIN ... END compound statement construct after FOR EACH ROW. (An example appears later in this section.)"

Collectives™ on Stack Overflow

Syntax error on MYSQL Trigger

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related