1. 触发器概述
MySQL从 5.0.2 版本开始支持触发器。
MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。
所谓事件就是指用户的动作或者某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。例如，向表1插入某些数据时，同时需要对表2中的数据进行相应的更新操作，可以使用触发器来实现，这样子就不用每次主动写SQL语句来更新表2的数据。
2. 触发器的创建
2.1 创建触发器的语法
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW 触发器执行的语句块;
- 表名 ：表示触发器监控的对象。
BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。INSERT|UPDATE|DELETE：表示触发的事件。
INSERT表示插入记录时触发；UPDATE表示更新记录时触发；DELETE表示删除记录时触发。
FOR EACH ROW：表示只要有触发器监控的表中的行受影响，触发器都执行，叫行级触发器。oracle触发器中分行级触发器和语句级触发器，可不写 for each row，无论影响多少行都只执行一次。mysql 不支持语句触发器，所以必须写for each row。- 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。
2.2 代码示例
2.2.1 数据表的创建
CREATE DATABASE dbtest; USE dbtest; 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) );
2.2.2 创建触发器
示例一
创建名称为before_insert_test_trigger的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。
# 创建名称为 before_insert_test_trigger 的触发器，向 test_trigger 数据表插入数据之前，向 # test_trigger_log 数据表中插入before_insert的日志信息。 CREATE TRIGGER before_insert_test_trigger BEFORE INSERT ON test_trigger FOR EACH ROW BEGIN INSERT INTO test_trigger_log(t_log) VALUES ('before insert ...'); END;
# 向test_trigger数据表中插入数据 INSERT INTO test_trigger(t_note) VALUES ('ZS...'); # 查看数据表中的数据 SELECT * FROM test_trigger; SELECT * FROM test_trigger_log;
示例二
创建名称为after_insert_test_trigger的触发器，向test_trigger数据表插入数据之后，向test_trigger_log数据表中插入after_insert的日志信息。
# 创建名称为 after_insert_test_trigger 的触发器，向 test_trigger 数据表插入数据之后， # 向test_trigger_log数据表中插入after_insert的日志信息。 CREATE TRIGGER after_insert_test_trigger AFTER INSERT ON test_trigger FOR EACH ROW BEGIN INSERT INTO test_trigger_log(t_log) VALUES ('after insert ...'); END;
INSERT INTO test_trigger(t_note) VALUES ('LS...'); SELECT * FROM test_trigger; SELECT * FROM test_trigger_log;
示例三
定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败。
# 准备数据表 CREATE TABLE employees AS SELECT * FROM atguigudb.employees; CREATE TABLE department AS SELECT * FROM atguigudb.departments;
# 定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查 # 将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为'HY000'的错 # 误，从而使得添加失败。 CREATE TRIGGER salary_check_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN # NEW 关键字表示要插入的新的记录 # 查询领导的薪资 DECLARE mgr_sal DOUBLE(8, 2) DEFAULT 0; SELECT salary INTO mgr_sal FROM employees WHERE employee_id = NEW.manager_id; # 判断新纪录的薪资是否大于对应领导的薪资 IF NEW.salary > mgr_sal THEN # 自定义错误 # SQLSTATE 'HY000' 报错标识，SET MESSAGE_TEXT 设置报错信息 SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; END IF; END;
SELECT salary FROM employees WHERE employee_id = 103; INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary, manager_id) VALUES (300, 'ZS', 'ZS@qq.com', CURDATE(), 'AD_VP',10000,103);
3. 触发器的查看
3.1 查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
3.2 查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名;
SHOW CREATE TRIGGER salary_check_trigger;
3.3 从系统库information_schema的TRIGGERS表中查询
SELECT * FROM information_schema.TRIGGERS;
从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME LIKE 'salary_check_trigger';
4. 删除触发器
触发器也是数据库对象，删除触发器也用DROP语句。
语法：
DROP TRIGGER IF EXISTS 触发器名称;
DROP TRIGGER IF EXISTS after_insert_test_trigger; SHOW TRIGGERS;
5. 触发器的优缺点
5.1 优点
1、触发器可以确保数据的完整性。
2、触发器可以帮助我们记录操作日志。
3、触发器还可以用在操作数据前，对数据进行合法性检查。
5.2 缺点
1、触发器最大的一个问题就是可读性差。
2、相关数据的变更，可能会导致触发器出错。触发器创建时不会执行内部的SQL，触发器被触发时，可能由于内部SQL报错而导致报错。
6. 触发器注意点
注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
