mysql - MYSQL 触发器语法错误

Question

我的 MYSQL 触发器出现错误。我使用了在这里和互联网上其他地方找到的各种形式的语法，但似乎没有任何效果。以下是我使用的 3 个语法示例:

>         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;

我会感谢任何人可以提供的帮助。我正在使用 phpmyadmin。

Answer 1

为了让您的 MySQL 客户端不将终止 INSERT 语句的 ; 解释为 CREATE TRIGGER 语句的结尾，您必须通知它您希望使用其他语句分隔符。

在mysql命令行客户端，您可以使用 DELIMITER command 执行此操作.例如，要将语句分隔符更改为双分号:

DELIMITER ;;

然后你可以这样做:

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;;

但是，因为在这种情况下您的触发器只包含一个语句，所以您不需要使用 BEGIN ... END compound statement block因此可以避免完全更改分隔符:

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'
  )
;