MySQL 17 章——触发器

在实际开发中，我们经常会遇到这样的情况：有2个或者多个相关联的表，比如商品信息表和库存信息表，分别存放在两个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录

这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用事务包裹起来，确保这两个操作成为原子操作，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步，导致数据丢失

这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了 

一、触发器概述

1. MySQL从5.0.2版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序
2. 触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句的时候，就相当于事件发生了，就会自动激发触发器执行相应的操作
3. 当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现

二、触发器的创建

（1）创建触发器语法

1. 创建触发器的语法结构是：

   CREATE TRIGGER 触发器名称 
   {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
   FOR EACH ROW 
   触发器执行的语句块;

2. 说明：
   1. 表名：表示触发器监控的对象
   2. BEFORE|AFTER：表示触发的时间。BEFORE表示在事件之前触发，AFTER表示在事件之后触发
   3. INSERT|UPDATE|DELETE表示触发的事件（1）INSERT表示插入记录时触发（2）UPDATE表示更新记录时触发（3）DELETE表示删除记录时触发
   4. 触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块

（2）代码举例

1. 创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息

   CREATE TABLE test_trigger(
   id INT PRIMARY KEY AUTO_INCREMENT,
   t_note VARCHAR(30)
   );CREATE TABLE test_trigger_log(
   id INT PRIMARY KEY AUTO_INCREMENT,
   t_log VARCHAR(30)
   );SELECT * FROM test_trigger;
   SELECT * FROM test_trigger_log;DELIMITER //CREATE TRIGGER before_insert
   BEFORE INSERT ON test_trigger
   FOR EACH ROW
   BEGININSERT INTO test_trigger_log(t_log)VALUES('before_insert');
   END //DELIMITER ;INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

2. 定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败

   CREATE TABLE employees
   AS
   SELECT *
   FROM atguigudb.employees;DELIMITER //CREATE TRIGGER salary_check_trigger
   BEFORE INSERT ON employees
   FOR EACH ROW
   BEGINDECLARE mgr_salary DOUBLE(8,2);SELECT salary INTO mgr_salary FROM employees WHERE employee_id = NEW.manager_id;IF NEW.salary > mgr_salary THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';END IF;
   END //DELIMITER ;

   

三、查看、删除触发器

（1）查看触发器

1. 查看当前数据库的所有触发器的定义

   SHOW TRIGGERS;

   

2. 查看当前数据库中的某个触发器的定义

   SHOW CREATE TRIGGER 触发器名;

   

3. 从系统库information_schema的TRIGGERS表中查询"salary_check_trigger"触发器的信息

   SELECT * FROM information_schema.TRIGGERS;

   

（2）删除触发器

1. 触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：

   DROP TRIGGER  IF EXISTS 触发器名称;

四、触发器的优缺点

（1）优点

1. 触发器可以确保数据的完整性（比如商品信息表和库存信息表，分别存放在两个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录）
2. 触发器可以帮助我们记录操作日志
3. 触发器还可以用在操作数据前，对数据进行合法性检查（比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏……这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统）

（2）缺点

1. 触发器最大的一个问题就是可读性差
2. 相关数据的变更，可能会导致触发器出错（特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率）

（3）注意点

1. 注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活

五、课后练习一

CREATE TABLE emps
AS
SELECT employee_id,last_name,salary
FROM atguigudb.employees;

1. 复制一张emps表的空表emps_back，只有表结构，不包含任何数据

   CREATE TABLE emps_back
   AS
   SELECT *
   FROM emps
   WHERE 1 = 2;

2. 查询emps_back表中的任何数据

   SELECT * FROM emps_back;

3. 创建触发器emps_insert_trigger，每当向emps表中添加一条记录时，同步将这条记录添加到emps_back中

   DELIMITER //CREATE TRIGGER emps_insert_trigger
   AFTER INSERT ON emps
   FOR EACH ROW
   BEGININSERT INTO emps_back(employee_id,last_name,salary)VALUES(NEW.employee_id,NEW.last_name,NEW.salary);
   END //DELIMITER ;

4. 验证触发器是否起作用

   INSERT INTO emps
   VALUES(301,'Tom1',3600);SELECT * FROM emps_back;

六、课后练习二

1. 复制一张emps表的空表emps_back1，只有表结构，不包含任何数据

   CREATE TABLE emps_back1
   AS
   SELECT *
   FROM emps
   WHERE 1 = 2;

2. 查询emps_back1表中的数据

   SELECT * FROM emps_back1;

3. 创建触发器emps_del_trigger，每当向emps表中删除一条记录时，同步将删除的这条记录添加到emps_back1表中

   DELIMITER //CREATE TRIGGER emps_del_trigger
   AFTER DELETE ON emps
   FOR EACH ROW
   BEGININSERT INTO emps_back1VALUES(OLD.employee_id,OLD.last_name,OLD.salary);
   END //DELIMITER ;

4. 验证触发器是否起作用

   DELETE FROM emps
   WHERE employee_id = 100;SELECT * FROM emps_back1;