首页 > 编程笔记 > MySQL笔记 阅读：11

MySQL触发器（trigger）的用法（非常详细）

MySQL 触发器和存储过程一样，都是嵌入到 MySQL 的一段程序。

触发器是由事件来触发某个操作，这些事件包括 INSERT、UPDATAE 和 DELETE 语句。如果定义了触发程序，当数据库执行这些语句的时候，就会激发触发器执行相应的操作。触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

本节将通过实例来介绍触发器的含义、创建触发器、查看触发器、触发器的使用方法以及删除触发器。

MySQL创建触发器

触发器（trigger）是一个特殊的存储过程，不同的是，执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候，触发器就会被 MySQL 自动调用。比如，当对 fruits 表进行操作（INSERT、DELETE 或 UPDATE）时，就会激活它执行。

触发器可以查询其他表，而且可以包含复杂的 SQL 语句。它们主要用于满足复杂的业务规则或要求。例如，可以根据客户当前的账户状态控制是否允许插入新订单。

1) 创建只有一个执行语句的触发器

创建一个触发器的语法如下： 
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

【实例】创建只有一个执行语句的触发器，代码如下： 
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
首先，创建一个 account 表，表中有两个字段，分别为 acct_num 字段（定义为 int 类型）和 amount 字段（定义成浮点类型）；其次，创建一个名为 ins_sum 的触发器，触发的条件是向数据表 account 插入数据之前，对新插入的 amount 字段值进行求和计算。

代码执行如下： 
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account  FOR EACH ROW SET @sum = @sum + NEW.amount;

mysql>SET @sum =0;

mysql> INSERT INTO account VALUES(1,1.00), (2,2.00);

mysql> SELECT @sum;
+------+
| @sum |
+------+
| 3.00 |
+------+
首先，创建一个 account 表，在向表 account 插入数据之前，计算所有新插入的 account 表的 amount 值之和，触发器的名称为 ins_sum，条件是在向表插入数据之前触发。

2) 创建有多个执行语句的触发器

创建具有多个执行语句的触发器的语法如下： 
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW
BEGIN
   语句执行列表
END

【实例】创建一个包含多个执行语句的触发器，代码如下： 
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);
上面的代码创建了一个名为 testref 的触发器。这个触发器的触发条件是在向表 test1 插入数据前执行触发器的语句，具体执行的代码如下： 
mysql> INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);
4 个表中的数据如下： 
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)
执行结果显示，在向表 test1 插入记录的时候，test2、test3、test4 都发生了变化。从这个例子看 INSERT 触发了触发器，向 test2 中插入了 test1 中的值，删除了 test3 中相同的内容，同时更新了 test4 中的 b4，即与插入的值相同的个数。

MySQL查看触发器

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。可以通过命令来查看已经创建的触发器。

下面将介绍两种查看触发器的方法，分别是 SHOW TRIGGERS 语句和在 TRIGGERS 表中查看触发器信息。

1) 利用SHOW TRIGGERS语句查看触发器信息

通过 SHOW TRIGGERS 查看触发器的语句如下： 
SHOW TRIGGERS;

【实例】通过 SHOW TRIGGERS 语句查看一个触发器，代码如下： 
SHOW TRIGGERS;

创建一个简单的触发器，名称为 trig_update，每次在 account 表更新数据之后都会向名称为 myevent 的数据表中插入一条记录，数据表 myevent 定义如下： 
CREATE TABLE myevent
(
id int DEFAULT NULL,
evt_name char(20) DEFAULT NULL
) ;

创建触发器的执行代码如下： 
mysql> CREATE TRIGGER trig_update AFTER UPDATE ON account
   -> FOR EACH ROW INSERT INTO myevent VALUES (1,'after update');
Query OK, 0 rows affected (0.00 sec)

使用 SHOW TRIGGERS 命令查看触发器： 
mysql> SHOW TRIGGERS;
+------+-----+------+--------+------+-------+--------+-------+--------------------+--------
------------+------------------+
|Trigger|Event|Table|Statement|Timing|Created|sql_mode|Definer|character_set_client|collati
n_connection|Database Collation|
+------+-----+------+--------+------+-------+--------+-------+--------------------+--------
------------+------------------+
|ins_sum|INSERT|account| SET @sum = @sum + NEW.amount| BEFORE | NULL| | root@localhost|latin1|
latin1_wedish_ci    | latin1_swedish_ci |
| trig_update | UPDATE | account | INSERT INTO myevent VALUES (1,'after update')| AFTER| NULL
|  |root@localhost | utf8mb4            | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci|
+------+-----+------+--------+------+-------+--------+-------+--------------------+--------
------------+------------------+
2 rows in set (0.00 sec)
可以看到，信息显示比较混乱。如果在 SHOW TRIGGERS 命令的后面添加上“\G”，显示信息会比较有条理，执行情况如下： 
mysql>  SHOW TRIGGERS \G
*************************** 1. row ***************************
          Trigger: ins_sum
            Event: INSERT
            Table: account
         Statement: SET @sum = @sum + NEW.amount
           Timing: BEFORE
          Created: 2022-03-17 09:27:50.92
          sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
          Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
          Trigger: trig_update
            Event: UPDATE
            Table: account
         Statement: INSERT INTO myevent VALUES (1,'after update')
           Timing: AFTER
          Created: 2022-03-17 09:33:51.49
          sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
          Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
          Trigger: testref
            Event: INSERT
            Table: test1
         Statement: 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
           Timing: BEFORE
          Created: 2022-03-17 09:31:33.40
          sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
          Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

提示，SHOW TRIGGERS 语句查看当前创建的所有触发器信息，在触发器较少的情况下，使用该语句会很方便。如果要查看特定触发器的信息，可以直接从 INFORMATION_SCHEMA 数据库中的 TRIGGERS 表中查找。下面将介绍这种方法。

2) 在triggers表中查看触发器信息

在 MySQL 中，所有触发器的定义都存在 INFORMATION_SCHEMA 数据库的 TRIGGERS 表中，可以通过查询命令 SELECT 查看，具体的语法如下： 
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

【实例】通过 SELECT 命令查看触发器，代码如下： 
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME= 'trig_update'\G

上述命令通过 WHERE 来指定查看特定名称的触发器。指定触发器名称的执行情况如下： 
*************************** 1. row ***************************
         TRIGGER_CATALOG: def
          TRIGGER_SCHEMA: test_db
           TRIGGER_NAME: trig_update
      EVENT_MANIPULATION: UPDATE
     EVENT_OBJECT_CATALOG: def
     EVENT_OBJECT_SCHEMA: test_db
      EVENT_OBJECT_TABLE: account
           ACTION_ORDER: 1
        ACTION_CONDITION: NULL
        ACTION_STATEMENT: INSERT INTO myevent VALUES (1,'after update')
      ACTION_ORIENTATION: ROW
          ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
               CREATED: 2022-03-17 09:33:51.49
               SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
               DEFINER: root@localhost
     CHARACTER_SET_CLIENT: utf8mb4
     COLLATION_CONNECTION: utf8mb4_0900_ai_ci
     DATABASE_COLLATION: utf8mb4_0900_ai_ci
从上面的执行结果可以得知：
也可以不指定触发器名称，这样将查看所有的触发器，命令如下： 
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS \G
这个命令会显示 TRIGGERS 表中所有的触发器信息，读者自行测试一下。

MySQL触发器的使用

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。

触发程序与表相关，当对表执行 INSERT、DELETE 或 UPDATE 语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。例如，可以在从表中删除每一行之前或在更新每一行之后激活触发程序。

【实例】创建一个在 account 表插入记录之后更新 myevent 数据表的触发器，代码如下： 
CREATE TRIGGER trig_insert AFTER INSERT ON account FOR EACH ROW INSERT INTO myevent VALUES (2,'after insert');

上面的代码创建了一个 trig_insert 触发器，在向表 account 插入数据之后，会向表 myevent 插入一组数据，代码执行如下： 
mysql> CREATE TRIG?GER trig_insert AFTER INSERT ON account
   -> FOR EACH ROW INSERT INTO myevent VALUES (2, 'after insert ');
mysql>  INSERT INTO account VALUES (1,1.00), (2,2.00);
mysql> SELECT * FROM myevent;
+------+--------------+
| id   | evt_name     |
+------+--------------+
|   2  | after insert |
|   2  | after insert |
+------+--------------+
从执行的结果来看，创建了一个名称为 trig_insert 的触发器，在向 account 插入记录之后进行触发，执行的操作是向表 myevent 插入一条记录。

MySQL删除触发器

使用 DROP TRIGGER 语句可以删除 MySQL 中已经定义的触发器，删除触发器语句的基本语法格式如下： 
DROP TRIGGER [schema_name.]trigger_name
其中，schema_name 表示数据库名称，是可选的。如果省略了 schema，将从当前数据库中舍弃触发程序；trigger_name 是要删除的触发器的名称。

【实例】删除一个触发器，代码如下： 
DROP TRIGGER test_db.ins_sum;
上面的代码中 test 是触发器所在的数据库，ins_sum 是一个触发器的名称。代码执行如下： 
mysql> DROP TRIGGER test_db.ins_sum;
Query OK, 0 rows affected (0.00 sec)
触发器 ins_sum 删除成功。

相关文章