MySQL分析与整理 — 触发器

文章中所有操作均是在 MySQL 5.7 版本下进行的

触发器可以理解为和表有关系的特殊的存储过程，可以在 insert，update，delete 表中数据的时候触发执行。它是自动触发执行的，执行了预先编写好的若干条 SQL 语句。这些若干条 SQL 语句是一个事务操作，要么全部执行，要么都不执行。这些若干条 SQL 语句不能使用以显示或隐式方式开始或结束事务的语句，如start trans-action，commit 或 rollback等。

触发器的定义有那么几个要素必须要遵循：

• 触发器是定义在表上的。
• 触发器是针对表的 insert，update，delete 操作触发的。
• 触发器是在表进行 insert，update，delete 之前或者之后触发。
• load data 和 replace 语句，这两种语句也能引起触发器的触发。

注：load data 语句用于将一个文件装入到一个数据表中，相当与一系列的 insert 操作。replace 语句一般来说和 insert 语句很像，只是在表中有 primary key 或 unique索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，相当于 delete 和 insert 操作。

1 使用触发器

1.1 创建触发器

触发器创建语法：
create trigger trigger_name 
trigger_time trigger_event 
on table_name for each row 
[trigger_order]
trigger_body;
解释：
trigger_name：触发器的名称，自定义。
trigger_time：触发器的触发时间，before或after。
trigger_event：触发器的触发事件，insert，update，delete。
table_name：触发器定义在哪个表上的，在哪张表上建立的触发器。
trigger_body：触发器触发要执行的程序体，可以是一条SQL语句，也可以是用begin和end包含的一条或多条SQL语句。
trigger_order：可以省略，MySQL5.7之后加入的功能。用于定义多个触发器，使用follows(之后尾随)或precedes(在…之先)来选择触发器执行的先后顺序。

关于 for each row：表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次，我们管它叫行级触发器。如果是熟悉 Oracle 的朋友会知道，Oracle 的触发器中的 for each row 是可以不写的，如果不写 for each row 被叫做语句级触发器，不管影响多少行数据触发器仅仅运行一次。但是在 MySQL 中，是不支持语句级触发器的，所以 for each row 不能不写。

1.2 查看触发器

在 MySQL 中，所有触发器信息都存储在 information_schema 数据库下的 triggers 表中，可以使用 select 语句查询，如果触发器信息过多，最好通过 trigger_name 字段指定或模糊查询。

select * from information_schema.triggers 
where trigger_name = '触发器名'\G;
-- 或者
select * from information_schema.triggers 
where trigger_name like '%触发器名%'\G;

-- 还可以用 show trigger\G; 查看，没有条件是查看全部
show trigger [from db_name] [like expr];
-- 或者
show trigger\G;

1.3 使用触发器

insert型触发器：插入某一行时激活触发器，通过insert，load data，replace语句触发。
update型触发器：更改某一行时激活触发器，通过update语句触发。
delete型触发器：删除某一行时激活触发器，通过delete，replace语句触发。

MySQL中定义了new/NEW（大小学通用）和old/OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。
在insert型触发器中，new用来表示将要（BEFORE）或已经（AFTER）插入的新数据。
在update型触发器中，old用来表示将要或已经被修改的原数据，new用来表示将要或已经修改为的新数据。
在delete型触发器中，old用来表示将要或已经被删除的原数据。
使用方法：new.columnName（columnName为相应数据表某一列名）
old是只读的，而new则可以在触发器中使用set赋值，不会再次触发触发器，造成循环调用。

触发器的执行顺序（InnoDB 存储引擎下的数据库）：

• 如果 before 类型的触发器执行失败，sql 无法正确执行。
• sql 执行失败时，after 类型的触发器不会触发。
• after 类型的触发器执行失败，sql 会回滚。

MySQL 的触发器是按照 before 触发器，行操作，after 触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果对非事务表进行操作，那么就无法回滚，数据可能会出错。

1.4 删除触发器

drop trigger [if exists] [schema_name.]trigger_name;
schema_name是数据库的名称，如果省略了schema_name，删除当前数据库中触发器。
trigger_name是要删除的触发器的名称。
if exists是为了避免在没有触发器的情况下删除触发器。
注：
	执行drop trigger语句需要SUPER权限。
	删除一个表的同时，也会自动删除该表上的触发器。
	触发器不能更新或覆盖，修改触发器是先删除再重新创建。

2 触发器应用示例

/*
有两个表，商品表，订单表
*/
-- 商品表
drop table if exists tbl_product;
create table tbl_product(
	pid int primary key,	-- 产品编号
	pd_name varchar(10),	-- 商品名
	pd_price double,		-- 商品价格
	pd_num int				-- 商品库存数量
) ENGINE=InnoDB default CHARSET=utf8;
-- 插入3条初始数据
insert into tbl_product values 
(1001, '花生', 50, 2), (1002, '毛豆', 60, 8), (1003, '奶糖', 100, 10);
-- 订单表
drop table if exists tbl_orders;
create table tbl_orders(
	oid int primary key auto_increment,	-- 订单id自增长
	pid int,	-- 订单的产品编号
	pnum int	-- 订单的产品数量
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*
业务描述：
	在订单表上创建触发器，当插入订单之后，会根据订单的产品编号和数量自动减少产品的数量。
*/
-- 创建触发器
delimiter $
create trigger trigger_orders 
after insert on tbl_orders for each row 
begin
	update tbl_product set pd_num = pd_num - new.pnum where pid = new.pid;
end
$
delimiter ;	-- 记得改回来
/*
以上命令解释：
因为begin和end之间可以是一条或者多条sql语句组合，每条sql语句都是以“;”英文分号结束。
MySQL数据库默认是以“;”英文分号认为结束和执行命令，所以会出现第一条sql的分号之后到end的命令不认。
通过delimiter命令临时改变MySQL的默认结束命令为其它字符，这里改是了“$”。
通知MySQL这个命令是到“$”结束。
不用了，记得改回来，用“delimiter ;”命令恢复为英文分号。
其中触发器中的new是存放插入的订单记录。
*/
-- 插入执行测试数据，向tbl_orders插入数据
insert into tbl_orders (pid, pnum) values (1002, 5);
insert into tbl_orders (pid, pnum) values (1003, 6);
-- 查看商品表中的库存变化，库存发生变化，说明触发器起作用了。
select * from tbl_product;
+------+---------+----------+--------+
| pid  | pd_name | pd_price | pd_num |
+------+---------+----------+--------+
| 1001 | 花生    |       50 |      2 |
| 1002 | 毛豆    |       60 |      3 |
| 1003 | 奶糖    |      100 |      4 |
+------+---------+----------+--------+

以上示例其实会出现库存不够的情况，MySQL 不能在触发器中通过回滚事务取消操作，其实我们可以把触发器设计的更完美一些。如下：

drop trigger trigger_orders;
delimiter $
create trigger trigger_orders 
before insert on tbl_orders for each row 
begin
	-- 定义一个变量num
	declare num int;
	-- 查询出要插入订单商品的库存数量赋值给定义的变量num
	select pd_num into num from tbl_product where pid = new.pid;
	-- 判断 如果库存数据量>=订单的数量，执行插入订单，并修改相应库存
	if num >= new.pnum then
		update tbl_product set pd_num = pd_num - new.pnum where pid = new.pid;
	end if;
end
$
delimiter ;	-- 记得改回来
-- 插入数据
insert into tbl_orders (pid, pnum) values (1001, 5);
-- 查看商品表中的库存变化，1001库存不够5，未发生变化。
select * from tbl_product;
+------+---------+----------+--------+
| pid  | pd_name | pd_price | pd_num |
+------+---------+----------+--------+
| 1001 | 花生    |       50 |      2 |
| 1002 | 毛豆    |       60 |      3 |
| 1003 | 奶糖    |      100 |      4 |
+------+---------+----------+--------+
-- 再次插入数据
insert into tbl_orders (pid, pnum) values (1001, 1);
-- 查看商品表中的库存已变化，1001库存大于1，库存发生改变
select * from tbl_product;
+------+---------+----------+--------+
| pid  | pd_name | pd_price | pd_num |
+------+---------+----------+--------+
| 1001 | 花生    |       50 |      1 |
| 1002 | 毛豆    |       60 |      3 |
| 1003 | 奶糖    |      100 |      4 |
+------+---------+----------+--------+
/*
其实我们还可以设计的更完美一些，比如库存不够，订单数据都不能插入，这里就不再分析了。
*/

其实我们还可以设计的更完美一些，比如库存不够，订单数据就不能插入。

drop trigger trigger_orders;
delimiter $
create trigger trigger_orders 
before insert on tbl_orders for each row 
begin
	-- 定义一个变量num
	declare num int;
	-- 查询出要插入订单商品的库存数量赋值给定义的变量num
	select pd_num into num from tbl_product where pid = new.pid;
	-- 判断 如果库存数据量>=订单的数量，执行插入订单，并修改相应库存
	if num >= new.pnum then
		update tbl_product set pd_num = pd_num - new.pnum where pid = new.pid;
	else
		-- 订单不够
		signal sqlstate 'HY000' set message_text = '库存不够禁止插入订单';
	end if;
end
$
delimiter ;	-- 记得改回来
-- 插入数据
insert into tbl_orders (pid, pnum) values (1001, 5);
ERROR 1644 (HY000): 库存不够禁止插入订单

结语

触发器的应用，随着项目或者产品的开发已经很少用了，很多业务处理和逻辑处理都已经移到程序层面上去判断和处理了，况且触发器在一定程度上影响了表的存储速度。所以当不在需要某个触发器的时候，最好是删除掉，以免造成一些数据上的混乱。最后有个点就是：如果删除了某个表，这个表上曾经创建过的触发器将一并删除。