为 MySQL 数据库中的表创建触发器(语法错误)
我无法为 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:
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;
但我总是收到错误语法错误".我被卡住了,我做错了什么?我正在使用 MySQL 5.0.51a-community
But always I get the error "wrong syntax". I got stuck, what am I doing wrong? I'm using 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;
但这也失败了:
CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
IF 1=1 THEN
END IF;
END;
这是我第一次使用 stackoverflow.com,所以如果在这里发布一些东西有帮助,我很兴奋:-)
It's my first time to use stackoverflow.com, so I'm very excited if it is helpful to post something here :-)
推荐答案
您需要 更改分隔符 - MySQL 看到第一个;"作为 CREATE TRIGGER 语句的结尾.
You need to change the delimiter - MySQL is seeing the first ";" as the end of the CREATE TRIGGER statement.
试试这个:
/* 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 ;
相关文章