MySQL数据库DDL之触发器应用

1、触发器作用：简单来说，触发器就是绑定在某个表上的一个特定数据库对象，当在这个表上发生某种触发器所监听的操作时，将会触发某种动作。

2、触发器用法：

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW
BEGIN
    ...trigger_statement... #触发器的逻辑实现
END

参数解释：

trigger_name:触发器的名称
trigger_event:触发的事件，包括：INSERT，UPDATE，DELETE
trigger_time:触发的时间点，包括：BEFORE（事件之前触发），AFTER（事件之后触发）
table_name:触发器所在表
trigger_statement:触发器被触发之后，所执行的数据库操作逻辑，可以为单一的数据库操作，或者一系列数据库操作集合，也可以包含一些判断等处理逻辑；

注意：
（1）同一张表中不能同时存在两个类型一样的触发器；

（2）触发事件和触发时间点总共可以组成3组6种不同的触发器，分别为：（BEFORE INSERT，AFTER INSERT）、（BEFORE UPDATE，AFTER UPDATE）、（BEFORE DELETE，AFTER DELETE）；

（3）触发事件：
① INSERT：在插入数据的时候触发，插入数据的动作包括INSERT，LOAD DATA，REPLACE操作，即：发生这三种操作时，都会触发INSERT类型的触发器；
② UPDATE：数据发生变更时触发，即：发生了UPDATE操作；
③ DELETE：从表中删除某一行的时候触发，即：发生了DELETE或者REPLACE操作；

（4）创建触发器的时候，由于在触发器的trigger_statement语句中有逻辑，而每个逻辑都会有结束符，默认为";"，故需要在创建之前先定义定界符。防止SQL语句在执行之前被存储引擎（存储引擎:MySQL数据库的插件，后续介绍）解析的时候碰见";"而提前结束，提示语法错误。

3、示例：

（1）示例1：现在test_db中有两个表，一个为员工信息表t_emp，一个为部门统计表t_dept_statis，他们的表结构分别如下所示：

员工信息表：

CREATE TABLE `t_emp` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '', #员工姓名
  `age` TINYINT(4) DEFAULT NULL,          #年龄
  `gender` ENUM('F','M') DEFAULT NULL,    #性别
  `dept_id` INT,                          #部门编号
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

部门员工数量统计表：

CREATE TABLE t_dept_statis(
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_count INT, #员工数量，初始化为0
    dept_id INT,   #部门编号
    update_time DATETIME #更新时间
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

初始化的统计数据，插入两条统计数据，分别为编号为1和2的两个部门，初始化员工数量为0：

mysql> INSERT INTO t_dept_statis(emp_count,dept_id,update_time) VALUES(0,1,NOW());
mysql> INSERT INTO t_dept_statis(emp_count,dept_id,update_time) VALUES(0,2,NOW());

需求：使用触发器实现每新增一条员工记录，部门信息统计表中就可以自动统计出员工数有变化的部门的员工总数量。这个需求可能不合适，但是完全可以说明触发器的用法：

mysql> \d $  #建立定界符，可以使用"DELIMITER $"，和"\d $"等价
mysql> CREATE TRIGGER dep_tri AFTER INSERT ON t_emp FOR EACH ROW
        BEGIN
            DECLARE num INT;
            SET num = (SELECT emp_count FROM t_dept_statis WHERE dept_id = new.dept_id);
            UPDATE t_dept_statis SET emp_count = num + 1 ,dept_id = new.dept_id, update_time = now() where dept_id = new.dept_id;
        END$
mysql> \d ;   #重新还原定界符为默认的定界符";"

#查看t_emp中的数据，此时是空的
mysql> SELECT * FROM t_emp;
Empty set (0.00 sec)

查看t_dept_statis中的数据，此时有两条初始化数据，员工数量为0

mysql> SELECT * FROM t_dept_statis;
+----+-----------+---------+-------------+
| id | emp_count | dept_id | update_time |
+----+-----------+---------+-------------+
|  1 |         0 |       1 | NULL        |
|  2 |         0 |       2 | NULL        |
+----+-----------+---------+-------------+

向t_emp中插入一条数据，然后查看t_dept_statis表，会发现，员工数量会自动统计

mysql> INSERT INTO t_emp(name,age,gender,dept_id) values('emp01',23,'F',1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t_dept_statis;
+----+-----------+---------+---------------------+
| id | emp_count | dept_id | update_time         |
+----+-----------+---------+---------------------+
|  1 |         1 |       1 | 2018-05-14 22:51:15 |
|  2 |         0 |       2 | NULL                |
+----+-----------+---------+---------------------+

再次向t_emp中插入一条数据，然后查看t_dept_statis表，会发现，员工数量会再次统计

mysql> INSERT INTO t_emp(name,age,gender,dept_id) values('emp03',26,'M',2);   
Query OK, 1 row affected (0.15 sec)
mysql> SELECT * FROM t_dept_statis;
+----+-----------+---------+---------------------+
| id | emp_count | dept_id | update_time         |
+----+-----------+---------+---------------------+
|  1 |         1 |       1 | 2018-05-14 22:51:15 |
|  2 |         1 |       2 | 2018-05-14 22:51:30 |
+----+-----------+---------+---------------------+

查看t_emp中的数据，会发现目前有两条记录，部门1和部门二中各有一条，统计表已经通过触发器实现了员工数量的自动统计：

mysql> SELECT * FROM t_emp;
+----+-------+------+--------+---------+
| id | name  | age  | gender | dept_id |
+----+-------+------+--------+---------+
|  1 | emp01 |   23 | F      |       1 |
|  2 | emp03 |   26 | M      |       2 |
+----+-------+------+--------+---------+
2 rows in set (0.00 sec)

（2）示例2：在test_db中有一张用户表t_user和t_user_bak，表结构相同，如下所示：

mysql> CREATE TABLE t_user(id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL DEFAULT '', #用户名
        age TINYINT NOT NULL DEFAULT 0,       #年龄
        create_time DATETIME NOT NULL         #创建时间
   ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
   
 mysql> CREATE TABLE t_user_bak(id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL DEFAULT '', #用户名
        age TINYINT NOT NULL DEFAULT 0,       #年龄
        create_time DATETIME NOT NULL         #创建时间
   ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;

创建测试数据，插入如下几条数据：

mysql> INSERT INTO t_user(name,age,create_time) VALUES('name01',23,NOW());
mysql> INSERT INTO t_user(name,age,create_time) VALUES('name02',25,NOW());

需求：如果t_user表中的数据被修改，则将修改前的数据先备份到t_user_bak表中，使用触发器实现：

mysql> \d $
mysql> CREATE TRIGGER user_bak_tri BEFORE UPDATE ON t_user FOR EACH ROW
       BEGIN
            INSERT INTO t_user_bak(name,age,create_time) VALUES(old.name,old.age,NOW());
       END$
mysql> \d ;

查询t_user_bak表中的数据，此时为空：

mysql> SELECT * FROM t_user_bak;
Empty set (0.00 sec)

修改t_user表中id为1的数据，然后再次查看t_user_bak表中的数据：

mysql> UPDATE t_user SET name = 'name001' WHERE name = 'name01';
mysql> SELECT * FROM t_user_bak;
+----+--------+-----+---------------------+
| id | name   | age | create_time         |
+----+--------+-----+---------------------+
|  1 | name01 |  23 | 2018-05-15 05:07:40 |
+----+--------+-----+---------------------+
1 row in set (0.00 sec)

可见，数据已经自动备份到t_user_bak中。

4、触发器中的new和old关键字：

（1）作用：用来访问受触发器影响的行中的列
（2）用法：

a、在INSERT操作中，new表示将要插入（BEFORE INSERT）或者已经插入（AFTER INSERT）表中的数据；
b、在UPDATE操作中，new表示将要插入或者已经插入的新数据，而old表示将要插入或者已经插入的原数据；
c、在DELETE操作中，old表示将要删除或者已经被删除的原数据；
d、OLD是只读的，而NEW则可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用；

5、触发器管理：

（1）查看已经创建好的触发器：
语法：

mysql> USE db_name;    #选择数据库
mysql> SHOW TRIGGERS;  #查看选择的数据库中已经创建的所有触发器
mysql> SHOW CREATE TRIGGER trigger_name; #查看某个触发器的创建过程

示例：查看test_db库中已经创建好的所有触发器：

mysql> USE test_db;  #选择数据库
mysql> SHOW TRIGGERS \G     #查看该库中的触发器
*************************** 1. row ***************************
             Trigger: dep_tri
               Event: INSERT
               Table: t_emp
           Statement: BEGIN
DECLARE num INT;
SET num = (SELECT emp_count FROM t_dept_statis WHERE dept_id = new.dept_id);
UPDATE t_dept_statis SET emp_count = num + 1 ,dept_id = new.dept_id, update_time = now() where dept_id = new.dept_id;
END
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.13 sec)

参数解释：

Trigger：触发器名称
Event：触发器所绑定的事件，即：发生什么操作时会执行触发器程序
Table：触发器所在的表
Statement：触发器的逻辑
Timing：触发器的事件
Created：表示创建时间
sql_mode：sql模式，STRICT_TRANS_TABLES表示当一个数据不能插入到一个事务表中，则中断当前操作，NO_ENGINE_SUBSTITUTION表示编译的时候如果没有选择默认存储引擎，则会使用一个默认的存储引擎，并提示一个错误；
Definer：创建触发器的用户
character_set_client：客户端使用的字符集
collation_connection：连接数据库使用的字符校验集
Database Collation：数据库使用的字符校验集

除此之外，还可以使用information_schema库中的trigger表查看已经存在的触发器，如下：

mysql> USE information_schema;
mysql> SELECT TRIGGER_SCHEMA AS 'db_name',EVENT_OBJECT_TABLE as 'table_name',TRIGGER_NAME as 'trigger_name',ACTION_STATEMENT AS 'trigger_statement' FROM TRIGGERS \G
*************************** 1. row ***************************
          db_name: test_db
       table_name: t_emp
     trigger_name: dep_tri
trigger_statement: BEGIN
DECLARE num INT;
SET num = (SELECT emp_count FROM t_dept_statis WHERE dept_id = new.dept_id);
UPDATE t_dept_statis SET emp_count = num + 1 ,dept_id = new.dept_id, update_time = now() where dept_id = new.dept_id;
END
2 rows in set (0.02 sec)

（2）删除指定的触发器：
语法：

mysql> DROP TRIGGER trigger_name;

示例：删除t_emp表上的dep_tri索引：

mysql> DROP TRIGGER dep_tri;

6、触发器的优缺点：

优点：
可以方便而且高效的维护数据；

缺点：
a、高并发场景下容易导致死锁，拖死数据库，成为数据库瓶颈，故高并发场景下一定要慎用；
b、触发器比较多的时候不容易迁移，而且表之间数据导入和导出可能会导致无意中触发某个触发器，造成数据错误，故对于数据量比较大，而且数据库模型非常复杂的情况下慎用；

7、事务场景下的注意要点：

MySQL中使用了插件式的存储引擎（存储引擎后文会详细介绍），对于InnoDB事务型的存储引擎，如果SQL语句执行错误，或者触发器执行错误，会发生什么结果呢？
（1）如果触发器或者SQL语句执行过程中出现错误，则会发生事务的回滚；
（2）SQL语句如果执行失败，则AFTER类型的触发器不会执行；
（3）如果AFTER类型的触发器执行失败，则触发此触发器的SQL语句将会回滚；
（4）如果BEFORE类型的触发器执行失败，则触发此触发程序的SQL语句将会执行失败；

至此，触发器相关内容介绍完毕，下一个章节介绍存储过程和函数，欢迎转发，讨论，共同学习~