23.3.1 触发器语法和示例

要创建触发器或删除触发器，请使用第 13.1.20 节“ CREATE TRIGGER 语句”和第 13.1.31 节“ DROP TRIGGER 语句”中所述的CREATE TRIGGER或DROP TRIGGER语句。

这是一个简单的示例，将触发器与 table 相关联，以激活INSERT操作。触发器充当累加器，将插入 table 中各列之一的值相加。

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语句创建与accounttable 关联的名为ins_sum的触发器。它还包括指定触发器动作时间，触发器事件以及触发器激活时的操作的子句：

关键字BEFOREtable 示触发动作时间。在这种情况下，触发器将在插入 table 中的每一行之前激活。另一个允许的关键字是AFTER。
关键字INSERTtable 示触发事件；即激活触发器的操作类型。在示例中，INSERT操作导致触发器激活。您还可以为DELETE和UPDATE操作创建触发器。
FOR EACH ROW后面的语句定义了触发器主体；也就是说，每当触发触发器时执行的语句，对于受触发事件影响的每一行都会发生一次。在该示例中，触发器主体是一个简单的SET，它将插入到amount列中的值累积到用户变量中。该语句将列称为NEW.amount，这意味着“要插入到新行中的amount列的值”。

若要使用触发器，请将累加器变量设置为零，执行INSERT语句，然后查看该变量之后具有什么值：

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

在这种情况下，执行INSERT语句后的@sum的值为14.98 + 1937.50 - 100或1852.48。

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

mysql> DROP TRIGGER test.ins_sum;

如果删除 table，该 table 的所有触发器也将被删除。

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

从 MySQL 5.7.2 开始，可以为给定的 table 定义具有相同触发事件和动作时间的多个触发。例如，一个 table 可以有两个BEFORE UPDATE触发器。默认情况下，具有相同触发事件和动作时间的触发器将按照其创建 Sequences 进行激活。要影响触发 Sequences，请在FOR EACH ROW之后指定一个子句，该子句指示FOLLOWS或PRECEDES，以及一个现有触发器的名称，该触发器也具有相同的触发事件和动作时间。使用FOLLOWS，新触发器将在现有触发器之后激活。使用PRECEDES，新触发器将在现有触发器之前激活。

例如，以下触发器定义为accounttable 定义了另一个BEFORE INSERT触发器：

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之后创建的。

在 MySQL 5.7.2 之前，给定 table 不能有多个具有相同触发事件和动作时间的触发。例如，一个 table 不能有两个BEFORE UPDATE触发器。要解决此问题，您可以定义触发器，通过使用FOR EACH ROW之后的开始...结束复合语句构造来执行多个语句。 (示例将在本节后面显示.)

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

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

以OLD命名的列是只读的。您可以引用它(如果拥有SELECT特权)，但不能对其进行修改。如果您具有_特权，则可以引用以NEW命名的列。在BEFORE触发器中，如果您具有UPDATE特权，也可以使用SET NEW.col_name = value更改其值。这意味着您可以使用触发器来修改要插入到新行中或用于更新行的值。 (这样的SET语句在AFTER触发器中无效，因为行更改已经发生.)

在BEFORE触发器中，AUTO_INCREMENT列的NEW值为 0，而不是实际插入新行时自动生成的序列号。

通过使用开始...结束构造，您可以定义执行多个语句的触发器。在BEGIN块中，您还可以使用存储的例程中允许的其他语法，例如条件和循环。但是，就像存储例程一样，如果使用mysql程序定义执行多个语句的触发器，则必须重新定义mysql语句定界符，以便可以在触发器定义中使用;语句定界符。下面的示例说明了这些要点。它定义了一个UPDATE触发器，该触发器将检查用于更新每一行的新值，并将该值修改为介于 0 到 100 之间的值。这必须是BEFORE触发器，因为必须先检查该值才能用于更新该行：

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语句来调用将数据返回给 Client 端或使用动态 SQL 的存储过程。 (允许存储过程通过OUT或INOUT参数将数据返回到触发器.)
触发器不能使用显式或隐式开始或结束事务的语句，例如START TRANSACTION，COMMIT或ROLLBACK。 (允许回滚到保存点，因为它不结束事务。)。

另请参见第 23.8 节“对存储程序的限制”。

MySQL 在触发器执行过程中按以下方式处理错误：

如果BEFORE触发器失败，则不执行相应行上的操作。
无论尝试随后是否成功，“尝试”都会激活BEFORE触发器以插入或修改该行。
仅当有任何BEFORE触发器并且行操作成功执行时，才会执行AFTER触发器。
BEFORE或AFTER触发器期间发生错误会导致导致触发调用的整个语句失败。
对于事务 table，语句失败应导致该语句执行的所有更改的回滚。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务 table，无法执行这种回滚，因此，尽管该语句失败，但是在错误点之前执行的任何更改仍然有效。

触发器可以按名称包含对 table 的直接引用，例如本示例中所示的名为testref的触发器：

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

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

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

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

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)

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35