在MySQL数据库中为表创建触发器(语法错误)

I have trouble defining a trigger for a MySQL database. I want to change a textfield before inserting a new row (under a given condition). This is what I have tried:

我无法定义MySQL数据库的触发器。在插入新行(在给定条件下)之前，我想要更改一个textfield。这就是我所尝试的:

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
  IF (NEW.sHeaders LIKE "%support@mydomain.com%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:internal@mydomain.com";
  END IF;
END;

But always I get the error "wrong syntax". I got stuck, what am I doing wrong? I'm using MySQL 5.0.51a-community

但是我总是会得到错误的语法。我被卡住了，我做错了什么?我使用MySQL 5.0.51a-community

BTW: Creating an empty Trigger like this works fine:

顺便说一句:创建这样一个空的触发器是可行的:

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
END;

But this fails, too:

但这失败:

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue 
FOR EACH ROW BEGIN
  IF 1=1 THEN
  END IF; 
END;

It's my first time to use *.com, so I'm very excited if it is helpful to post something here :-)

这是我第一次使用*.com，如果能在这里发布一些有用的东西，我很兴奋:

1 个解决方案

#1

You need to change the delimiter - MySQL is seeing the first ";" as the end of the CREATE TRIGGER statement.

您需要更改分隔符—MySQL将第一个“;”作为CREATE触发器语句的结尾。

Try this:

试试这个:

/* Change the delimiter so we can use ";" within the CREATE TRIGGER */
DELIMITER $$

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
  IF (NEW.sHeaders LIKE "%support@mydomain.com%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:internal@mydomain.com";
  END IF;
END$$
/* This is now "END$$" not "END;" */

/* Reset the delimiter back to ";" */
DELIMITER ;

#1