相信过希望，却被希望抛弃，那么，你还会再次相信它吗?
二八佳人体似酥，腰悬利剑斩愚夫，虽然不见人头落,暗里教君骨髓枯。
上一章简单介绍了 MySQL函数(二十五) ,如果没有看过,请观看上一章
在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，
如商品信息和库存信息分别存放在 2 个不同的数据表中，
我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。
这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用事务包裹起来，确保这两个操作成为一个原子操作，
要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步，
导致数据缺失。
这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。
这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。
一. 触发器概述
MySQL从5.0.2版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。
所谓事件就是指用户的动作或者触发某项行为。
如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
一.一 三种触发器 INSERT, UPDATE, DELETE
在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。
在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。
1) INSERT 触发器
在 INSERT 语句执行之前或之后响应的触发器。
使用 INSERT 触发器需要注意以下几点：
- 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
- 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
- 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。
2) UPDATE 触发器
在 UPDATE 语句执行之前或之后响应的触发器。
使用 UPDATE 触发器需要注意以下几点：
- 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
- 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
- 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
- OLD 中的值全部是只读的，不能被更新。
注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。
3) DELETE 触发器
在 DELETE 语句执行之前或之后响应的触发器。
使用 DELETE 触发器需要注意以下几点：
- 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
- OLD 中的值全部是只读的，不能被更新。
总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。
若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。
若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。
若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。
仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。
一.二 创建触发器
创建触发器的语法结构是：
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;说明：
表名：表示触发器监控的对象。BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。INSERT|UPDATE|DELETE：表示触发的事件。
- INSERT 表示插入记录时触发；
- UPDATE 表示更新记录时触发；
- DELETE 表示删除记录时触发。
触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。
触发时间两种， 触发事件三种， 一共是 2*3=6 种类型
创建一个两个表， 用户表和 用户日志表， 对用户的操作修改后，保存到 user_log 表里面
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '名称',
`sex` varchar(255) DEFAULT NULL COMMENT '性别',
`age` int DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `user_log` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`create_time` timestamp NULL DEFAULT NULL COMMENT '操作时间',
`description` varchar(255) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;一.三 创建触发器事例
使用 NEW.属性 表示要插入/插入的属性值
一.三.一 Before Insert
在插入到 user 之前，要求年龄大于 18岁
-- 切换标识符
delimiter //
-- 创建存储语句
create TRIGGER biUser
before insert on user
for each row
begin
if NEW.age < 18 then
SIGNAL SQLSTATE '50001' set MESSAGE_TEXT = 'user age must be older than 18';
end if;
-- 结束
end //
-- 恢复标识符为 ;
delimiter ;插入年龄大于 18岁的
insert into user(name,sex,age) values('岳泽霖','男',28);
插入 age <18 的，则失败
insert into user(name,sex,age) values('岳小霖','男',14);
一.三.二 AFTER INSERT
在插入表 user 之后，往 user_log 表里面也插入一条日志数据，进行记录
创建触发器
-- 切换标识符
delimiter //
-- 创建存储语句
create TRIGGER aiUser
AFTER insert on user
for each row
begin
DECLARE desc1 varchar(255) default '';
set desc1 = concat('姓名:',NEW.name,'性别:',NEW.sex,'年龄:',NEW.age);
insert into user_log (`create_time`,description) values (current_timestamp(),desc1);
end //
-- 恢复标识符为 ;
delimiter ;select * from user_log;
insert into user(name,sex,age) values('岳泽霖','男',28);
select * from user_log;插入之后， user_log 表里面有数据
一.三.三 BEFORE UPDATE
在修改之前进行验证
-- 切换标识符
delimiter //
-- 创建存储语句
create TRIGGER buUser
BEFORE UPDATE on user
for each row
begin
if NEW.age < 18 then
SIGNAL SQLSTATE '50001' set MESSAGE_TEXT = 'user age must be older than 18';
end if;
end //
-- 恢复标识符为 ;
delimiter ;-- 成功
update user set age = 29 where id = 1;
-- 修改失败
update user set age = 12 where id = 1;
一.三.四 AFTER UPDATE
OLD. 属性 表示之前的， NEW.属性 表示新记录的
-- 切换标识符
delimiter //
-- 创建存储语句
create TRIGGER auUser
AFTER UPDATE on user
for each row
begin
DECLARE desc1 varchar(255) default '';
DECLARE desc2 varchar(255) default '';
set desc1 = concat('之前姓名:',OLD.name,'之前性别:',OLD.sex,'之前年龄:',OLD.age);
set desc2 = concat('新姓名:',NEW.name,'新性别:',NEW.sex,'新年龄:',NEW.age);
insert into user_log (`create_time`,description) values (current_timestamp(),concat(desc1,desc2));
end //
-- 恢复标识符为 ;
delimiter ;-- 成功
update user set age = 30 where id = 1;
select * from user_log ;
一.三.五 BEFORE DELETE
在删除之前进行验证， 如果 ID 为 1的话，不让删除。
-- 切换标识符
delimiter //
-- 创建存储语句
create TRIGGER bdUser
BEFORE DELETE on user
for each row
begin
if OLD.id = 1 then
SIGNAL SQLSTATE '50002' set MESSAGE_TEXT = 'user can not delete id is 1';
end if;
end //
-- 恢复标识符为 ;
delimiter ;删除语句:
-- 可以删除
delete from user where id = 2;
-- 不能删除
delete from user where id = 1;一.三.六 AFTER DELETE
-- 切换标识符
delimiter //
-- 创建存储语句
create TRIGGER adUser
AFTER DELETE on user
for each row
begin
DECLARE desc1 varchar(255) default '';
set desc1 = concat('id:',OLD.id,'姓名:',OLD.name,'性别:',OLD.sex,'年龄:',OLD.age);
insert into user_log (`create_time`,description) values (current_timestamp(),desc1);
end //
-- 恢复标识符为 ;
delimiter ;-- 可以删除
delete from user where id = 3;
select * from user_log;
二. 触发器的其他操作
二.一 查看触发器
查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS
方式2：查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
二.二 删除触发器
触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：
DROP TRIGGER IF EXISTS 触发器名称;
三. 触发器的优缺点
三.一 优点
1、触发器可以确保数据的完整性。
2、触发器可以帮助我们记录操作日志。
利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助。
3、触发器还可以用在操作数据前，对数据进行合法性检查。
比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏……这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统。
三.三 缺点
1、触发器最大的一个问题就是可读性差。
因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。
比如，创建触发器用于修改会员储值操作。如果触发器中的操作出了问题，会导致会员储值金额更新失败。我用下面的代码演示一下：
mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'结果显示，系统提示错误，字段“aa”不存在。
这是因为，触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是会员信息表的结构出了问题。说不定你还会给会员信息表添加一个叫“aa”的字段，试图解决这个问题，结果只能是白费力。
2、相关数据的变更，可能会导致触发器出错。
特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。
注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
例如：基于子表员工表（t_employee）的DELETE语句定义了触发器t1，而子表的部门编号（did）字段定义了外键约束引用了父表部门表（t_department）的主键列部门编号（did），并且该外键加了“ON DELETE SET NULL”子句，那么如果此时删除父表部门表（t_department）在子表员工表（t_employee）有匹配记录的部门记录时，会引起子表员工表（t_employee）匹配记录的部门编号（did）修改为NULL，但是此时不会激活触发器t1。只有直接对子表员工表（t_employee）执行DELETE语句时才会激活触发器t1。
谢谢!!!
