MYSQL 触发器上的语法错误 [英] Syntax error on MYSQL Trigger

问题描述

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

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;

我愿意提供任何人可以提供的帮助.我正在使用 phpmyadmin.

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

推荐答案

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

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.

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

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

然后你可以这样做:

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 复合语句块，因此可以完全避免更改分隔符:

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

这篇关于MYSQL 触发器上的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！