MySQL 5.7 使用触发器

Posted on by YongJing(敬勇)

使用触发器
触发器是一个与表相关联的命名数据库对象，在表发生特定事件时激活。触发器的一些用途是检查将要插入到表中的值，或者对更新中涉及的值 进行计算。

定义了一个触发器，当一条语句插入、更新或删除关联表中的行时激活。这些行操作是触发事件。例如，可以通过INSERT或LOAD DATA语句插入 行，并且为每个插入行激活一个INSERT触发器。触发器可以设置为在触发事件之前或之后激活。例如，可以在插入到表中的每一行之前或更新每 一行之后激活触发器。

MySQL触发器仅在SQL语句对表进行更改时激活。这包括对基于可更新视图的基表的更改。如果api没有将SQL语句传输到MySQL服务器，则触发器 不会激活对表的更改。这意味着使用NDB API进行的更新不会激活触发器。

在INFORMATION_SCHEMA或performance_schema表中更改不会激活触发器。那些表实际上是视图，视图上不允许有触发器。

下面几节描述创建和删除触发器的语法，展示如何使用它们的一些示例，并指出如何获取触发器元数据。

额外资源
.在使用触发器时，您可以找到使用触发器的触发器用户论坛。

.有关MySQL中触发器的常见问题的答案

.对触发器的使用有一些限制

.触发器的二进制日志记录按所述进行

触发器语法和示例
要创建触发器或删除触发器，请使用create trigger或drop trigger语句

下面是一个简单的示例，它将触发器与表关联起来，以激活INSERT操作。触发器充当累加器，将插入到表的某一列中的值相加。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.01 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，表示“要插入到新行中的金额列 的值”。

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

mysql> SET @sum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+
1 row in set (0.00 sec)

在本例中，执行INSERT语句后@sum的值为14.98 + 1937.50 – 100，即1852.48。

要销毁触发器，使用DROP trigger语句。如果触发器不在默认模式中，则必须指定模式名：

mysql> DROP TRIGGER test.ins_sum;
Query OK, 0 rows affected (0.00 sec)

如果删除一个表，那么该表的任何触发器也会被删除。

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

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

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

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

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之前，对于一个给定的表，不能有多个触发器具有相同的触发事件和操作时间。例如，一个表不能有两个BEFORE UPDATE触发器。 为了解决这个问题，你可以定义一个执行多个语句的触发器，在FOR EACH FOW之后使用BEGIN…END复合语句构造。（本节后面会给出一个例子。 ）

在触发器主体中，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权限。这意味着您可以使用触发器来修改要插入新 行或用于更新行的值。（这样的SET语句在AFTER触发器中没有作用，因为行更改已经发生了。）

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

通过使用BEGIN … END构造时，你可以定义一个执行多个语句的触发器。在BEGIN语句块中，还可以使用存储例程允许的其他语法，如条件语句 和循环语句。然而，就像存储例程一样，如果你使用mysql程序定义了一个执行多条语句的触发器，那么就有必要重新定义mysql语句的定界符， 这样你就可以使用；触发器定义中的语句定界符。下面的例子说明了这些要点。它定义了一个更新触发器，检查用于更新每一行的新值，并将该 值修改为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;//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

单独定义存储过程，然后使用简单的CALL语句从触发器调用它，这样更容易。如果你想在多个触发器中执行相同的代码，这也是有利的。

当触发器被激活时，可以在语句中显示的内容是有限制的
.触发器不能使用CALL语句调用向客户机返回数据或使用动态SQL的存储过程。(允许存储过程通过OUT或INOUT参数)。

.触发器不能使用显式或隐式开始或结束事务的语句，例如START TRANSACTION， COMMIT， ROLLBACK。（回滚到SAVEPOINT是允许的，因为它不 结束事务。）
MySQL在触发器执行过程中处理错误如下：

.如果BEFORE触发失败，则不执行对应行的操作。

.当尝试插入或修改该行时，无论随后的尝试是否成功，都会激活BEFORE触发器。

.只有当任何BEFORE触发器和行操作成功执行时，才会执行AFTER触发器。

.BEFORE或AFTER触发器期间的错误将导致触发触发器调用的整个语句失败。

.对于事务性表，语句失败应导致回滚该语句执行的所有更改。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务性表， 不能进行这样的回滚，因此尽管语句失败，但在错误点之前执行的任何更改仍然有效。

触发器可以通过名称直接引用表，例如下面这个例子中的testref触发器：

mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test4(
    -> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> b4 INT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter |
mysql> 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;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> INSERT INTO test3 (a3) VALUES
    -> (NULL), (NULL), (NULL), (NULL), (NULL),
    -> (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test4 (a4) VALUES
    -> (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

假设在表test1中插入以下值，如下所示：

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

因此，这四个表包含以下数据：

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)

触发元数据
可以通过以下方式获取触发器的元数据：
.查询INFORMATION_SCHEMA数据库中的TRIGGERS表

.使用SHOW CREATE TRIGGER语句。

.使用SHOW TRIGGERS语句

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.