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

Question

我在定义MySQL数据库触发器时遇到了问题。我想在插入新行之前更改一个文本字段（在给定条件下）。这是我尝试过的：

CREATE TRIGGER add_bcc
BEFORE INSERT ON MailQueue
FOR EACH ROW BEGIN
  IF (NEW.sHeaders LIKE "[e-mail]%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:[e-mail]";
  END IF;
END; 

但是我总是得到“错误的语法”错误。我被卡住了，我做错了什么？我正在使用MySQL 5.0.51a-community

顺便说一句：创建一个空触发器像这样是可以正常工作的：

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，如果在这里发布一些内容有帮助，我会感到非常兴奋:-)

- Georg Ledermann

---

如果你移除了"SET"会发生什么？另外，你应该将NEW.sHeaders + "BCC:[e-mail]"改为CONCAT(NEW.sHeaders, "BCC:[e-mail]")。 - Greg

我将这个作为注释而不是答案，因为这只是我的猜测 :p - Greg

谢谢！我已经尝试了，但语法错误似乎是由if语句引起的。即使以下也失败了：CREATE TRIGGER add_bcc BEFORE INSERT ON MailQueue FOR EACH ROW BEGIN IF 1=1 THEN END IF; END;不幸的是，MySQL没有给出任何好的提示，说明错误是关于什么的... - Georg Ledermann

Answer 1

你需要更改分隔符- MySQL会将第一个“;”视为CREATE TRIGGER语句的结尾。

尝试这样做：

/* 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 "[e-mail]%") THEN
    SET NEW.sHeaders = NEW.sHeaders + "BCC:[e-mail]";
  END IF;
END$$
/* This is now "END$$" not "END;" */

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

- RoBorg

Answer 2

触发器可以在表格被改变之前或之后自动运行多个SQL语句，包括INSERT、UPDATE或DELETE操作。*文档详细解释了触发器的用法。

例如，你可以按照下面的示例创建一个test表格：

CREATE TABLE test (
  num int
);

然后，您将其插入行，其num为2，如下所示：

INSERT INTO test (num) VALUES (2);

接下来，您可以按照下面的示例创建log表：

CREATE TABLE log (
  ins INT,
  upd INT,
  del INT
);

然后，您插入以下显示的ins、upd和del均为0的行：

INSERT INTO test_log (ins, upd, del) VALUES (0, 0, 0);

现在，您可以创建一个触发器count，在test表的行更新后，将1添加到log表的upd字段中。如下所示。*您可以将AFTER和UPDATE替换为BEFORE和INSERT或DELETE，以在将行插入到test表之前或删除test表的行之前运行count触发器。在触发器中，允许使用SELECT INTO，但不允许使用没有INTO的SELECT，因为存在错误。基本上，当创建触发器时，您需要将默认分隔符;更改为类似$$的内容，否则会出错。然后，在创建触发器之后，您需要将分隔符$$更改回;，如下所示。我的答案解释了分隔符的使用方法，并且在创建存储过程时，您必须选择一个数据库，否则会出现错误。

DELIMITER $$

CREATE TRIGGER count
AFTER UPDATE ON test FOR EACH ROW
BEGIN
UPDATE log SET upd = upd + 1;
END$$

DELIMITER ;

然后，您在“test”表中将“num”从“2”更新为“4”，然后如下所示，“upd”为“1”。

mysql> UPDATE test SET num = 4;
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    4 |
+------+
...
mysql> SELECT * FROM log;       
+------+------+------+
| ins  | upd  | del  |
+------+------+------+
|    0 |    1 |    0 |
+------+------+------+

此外，您可以通过OLD.num和NEW.num获取更新前后的num值。*这些num值分别存储在用户定义的会话变量@old_num和@new_num中，您不能将OLD.num和NEW.num用于INSERT和DELETE，因为分别存在错误和错误。

DELIMITER $$

CREATE TRIGGER count
AFTER UPDATE ON test FOR EACH ROW
BEGIN
UPDATE log SET upd = upd + 1;
SET @old_num = OLD.num, @new_num = NEW.num;
END$$        -- ↑ Here           -- ↑ Here

DELIMITER ;

然后，在更新了test表中的num字段从2到4之后，@old_num和@new_num分别为2和4，如下所示：

然后，在更新了test表中的num字段从2到4之后，@old_num和@new_num分别为2和4，如下所示：

mysql> UPDATE test SET num = 4;
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    4 |
+------+
...
mysql> SELECT * FROM log;       
+------+------+------+
| ins  | upd  | del  |
+------+------+------+
|    0 |    1 |    0 |
+------+------+------+
...
mysql> SELECT @old_num, @new_num;
+----------+----------+
| @old_num | @new_num |
+----------+----------+
|        2 |        4 |
+----------+----------+

在下面的这种情况下，你可以创建一个不改变分隔符为$$的count触发器而不会出错。

CREATE TRIGGER count
AFTER UPDATE ON test FOR EACH ROW
UPDATE log SET upd = upd + 1;

- Super Kai - Kazuya Ito