MySQL :: MySQL 8.4 Reference Manual :: 27.3.1 Trigger Syntax and Examples

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Trigger Syntax and Examples

27.3.1 触发器语法和示例

要创建触发器或删除触发器，请使用CREATE TRIGGER或DROP TRIGGER语句，详见第15.1.22节，“CREATE TRIGGER 语句”和第15.1.34节，“DROP TRIGGER 语句”。

以下是一个简单的示例，用于将触发器与表相关联，以便在INSERT操作中激活。触发器作为累加器，累加插入到表中的值之一的列。

Press CTRL+C to copy
 
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

以下CREATE TRIGGER语句创建一个名为ins_sum的触发器，该触发器与account表相关联。该语句还包括子句，指定触发器的动作时间、触发事件和触发器激活时的操作：

关键字BEFORE表示触发器的动作时间。在这个例子中，触发器在每个插入到表中的行之前激活。其他允许的关键字是AFTER。
关键字INSERT表示触发事件，即触发器激活的操作类型。在示例中，INSERT操作激活触发器。您也可以创建触发器来处理DELETE和UPDATE操作。
语句FOR EACH ROW定义触发器体，即每次触发器激活时执行的语句。在示例中，触发器体是一个简单的SET，累加插入到amount列的值。语句引用列为NEW.amount，表示“将要插入到新行的amount列的值”。

要使用触发器，请将累加器变量设置为零，执行INSERT语句，然后查看变量的值：

Press CTRL+C to copy
 
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+

在这个例子中，@sum变量的值在INSERT语句执行后为14.98 + 1937.50 - 100，或1852.48。

要销毁触发器，请使用DROP TRIGGER语句。您必须指定架构名，如果触发器不在默认架构中：

Press CTRL+C to copy
 
mysql> DROP TRIGGER test.ins_sum;

如果您删除一个表，任何该表的触发器也将被删除。

触发器名称存在于架构命名空间中，这意味着所有触发器必须在架构中具有唯一的名称。来自不同架构的触发器可以具有相同的名称。

可以为给定的表定义多个触发器，这些触发器具有相同的触发事件和动作时间。例如，您可以为一个表定义两个BEFORE UPDATE触发器。默认情况下，具有相同触发事件和动作时间的触发器将按照它们的创建顺序激活。要影响触发器顺序，请在FOR EACH ROW后指定一个子句，该子句指示FOLLOWS或PRECEDES，并指定一个具有相同触发事件和动作时间的现有触发器的名称。使用FOLLOWS，新的触发器将在现有触发器后激活。使用PRECEDES，新的触发器将在现有触发器前激活。

例如，以下触发器定义定义了一个名为account的表的另一个BEFORE INSERT触发器：

Press CTRL+C to copy
 
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
       FOR EACH ROW PRECEDES ins_sum
       SET
       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)

这个触发器ins_transaction类似于ins_sum，但它累积存款和提款分别。它具有一个PRECEDES子句，该子句使其在ins_sum之前激活；否则，它将在ins_sum后激活，因为它是创建在ins_sum之后的。

在触发器体中，OLD和NEW关键字使您可以访问受触发器影响的行的列。OLD和NEW是MySQL对触发器的扩展；它们不是大小写敏感的。

在一个INSERT触发器中，只能使用NEW.col_name；没有旧行。在一个DELETE触发器中，只能使用OLD.col_name；没有新行。在一个UPDATE触发器中，您可以使用OLD.col_name来引用一个行在更新前的一列，并使用NEW.col_name来引用一个行在更新后的一列。

使用OLD命名的列是只读的。您可以引用它（如果您拥有SELECT权限），但不能修改它。您可以引用使用NEW命名的列，如果您拥有SELECT权限对它。在一个BEFORE触发器中，您可以使用SET NEW.col_name = value将其值更改为要插入的新行或要更新的行的值（如果您拥有UPDATE权限对它）。这意味着您可以使用触发器来修改要插入的新行或要更新的行的值。（在一个AFTER触发器中，这个SET语句没有效果，因为行的更改已经发生。）

在一个BEFORE触发器中，NEW值对于一个AUTO_INCREMENT列是0，而不是在实际插入新行时生成的顺序号。

使用BEGIN ... END构造，可以定义一个触发器，该触发器可以执行多个语句。在BEGIN块中，也可以使用其他语句，如条件语句和循环语句。然而，就像存储程序一样，如果使用mysql程序定义一个触发器，该触发器可以执行多个语句，需要重新定义mysql语句分隔符，以便在触发器定义中使用;语句分隔符。以下示例演示了这些点。它定义了一个UPDATE触发器，该触发器检查要用于更新每行的新值，并将其修改为在0到100之间的范围。这必须是一个BEFORE触发器，因为要检查的值必须在更新行之前检查：

Press CTRL+C to copy
 
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;

可以更容易地定义一个存储程序，然后从触发器中使用简单的CALL语句来调用该存储程序。这也是一种优势，如果你想在多个触发器中执行相同的代码。

触发器执行语句时存在一些限制：

触发器不能使用CALL语句来调用返回数据到客户端或使用动态SQL的存储程序。 (存储程序允许通过OUT或INOUT参数将数据返回到触发器。)
触发器不能使用隐式或显式地开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK。 (ROLLBACK to SAVEPOINT允许，因为它不结束事务。)

请参见第27.8节，“存储程序限制”.

MySQL 处理触发器执行错误如下：

如果一个BEFORE触发器失败，操作对应行将不执行。
一个BEFORE触发器在尝试插入或修改行时被激活，即使尝试最终失败。
一个AFTER触发器只有在所有BEFORE触发器和行操作都成功执行时才被执行。
在BEFORE或AFTER触发器出现错误时，整个导致触发器调用的语句将失败。
对于事务表，语句失败应该导致所有由语句执行的更改回滚。触发器失败也会导致语句失败，从而导致回滚。对于非事务表，回滚不能执行，因此尽管语句失败，但是在错误点之前执行的所有更改仍然生效。

触发器可以包含对表名的直接引用，例如在以下示例中所示的触发器名testref：

Press CTRL+C to copy
 
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

假设您将以下值插入表test1，如下所示：

Press CTRL+C to copy
 
mysql> INSERT INTO test1 VALUES 
       (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

结果，四个表包含以下数据：

Press CTRL+C to copy
 
mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

PREV HOME UP NEXT