MySQL触发器
触发器（trigger）是数据库中的一个很重要的、很实用的基于事件的处理器，在处理一些业务需求的时候，使用触发器会很方便。似乎在《高性能MySQL》中，对触发器作了一定的描述，也提到使用中的一些优势和局限性，但感觉还是不能完全理解触发器的全部功能和实现。于是自己在网上看了一些文章，结合官网（https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html）中的案例，写下这篇总结。
基本理解：
1. 使用场合：
触发器是基于事件的，主要的事件也就是MySQL的增删改操作，即insert,delete,update。
2. 触发器的命名
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
因为触发器在单表的命名空间内，所以同一个表的触发器名称需要不同。不同表可以有相同的触发器名称。
3. 触发器执行顺序
如果有相同的update（或者delete,insert）触发器，就会按照创建的时间来执行。
而FOLLOWS 和 PRECEDES 可以修改trigger的执行顺序
例如官方的案例：
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
FOR EACH ROW PRECEDES ins_sum
SET
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)
ins_transaction和ins_sum分别是两个触发器的名称。
4. 触发器的作用：
1. 安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
1）可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
2）可以基于数据库中的数据限制用户的操作，例如不允许单个商品的购买量大于一个固定值。
2. 审计。可以跟踪用户对数据库的操作。
1）审计用户操作数据库的语句。
2）把用户对数据库的更新写入审计表。
这一块因为本人没有用过，就不赘述了，以后有机会来补充
3. 实现复杂的数据完整性规则
实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
4. 实现复杂的非标准的数据库相关完整性规则。
1）触发器可以对数据库中相关的表进行连环更新。这是用得比较多的一种实现功能。
2） 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。
下面的例子我会比较详细的描述这两个特性。
5. 同步实时地复制表中的数据。
6. 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。
例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。
（1）插入数据：
当用户添加一个订单的时候，我们需要对商品表格中的库存（storage）进行相应的改动
mysql> create trigger shop_goods -> after insert on shoppingcar -> for each row -> update goods set storage=storage-new.amount where id=new.g_id -> ; Query OK, 0 rows affected (0.03 sec) mysql>insert into shoppingcar values(1,1,2);
查询结果：
goods表 +-------+---------+-------------+---------+ | id | gname | description | storage | +-------+---------+---------- --+---------+ | 1 | huawei | rongyao9 | 198 | | 2 | iphone | iphoneX | 100 | +-------+---------+-------------+---------+ shoppingcar表 +----- -+----- -+--------+ | u_id | g_id | amount | +-------+-------+--------+ | 1 | 1 | 2 | +-------+-------+--------+
关于new和old的使用
new表示新的数据行，而old表示旧的数据行
（2）删除数据
例如，用户撤销一个订单的时候，我们需要将商品的数量加回去
mysql> create trigger shop_good1 -> after delete on shoppingcar -> for each row -> update goods set storage=storage+old.amount where id=old.g_id; Query OK, 0 rows affected (0.01 sec)
删除前： mysql> select * from goods; +-----+---------+-------------+---------+ | id | gname | description | storage | +-----+---------+-------------+---------+ | 1 | huawei | rongyao9 | 198 | | 2 | iphone | iphoneX | 100 | +-----+---------+-------------+---------+ 2 rows in set (0.00 sec) mysql> select * from shoppingcar; +------+------+--------+ | u_id | g_id | amount | +------+------+--------+ | 1 | 1 | 2 | +------+------+--------+ 1 rows in set (0.00 sec)
删除数据： mysql> delete from shoppingcar where g_id=1; Query OK, 1 row affected (0.03 sec)
结果：
（3）更新数据（可增可减）
当用户对想通过修改购物车的数量来修改自己购买某种商品的数量，那么，我们的库存也需要跟着改动。
mysql> create trigger shop_good2 -> after update on shoppingcar -> for each row -> update goods set storage=storage-new.amount+old.amount where id=new.g_id/old.g_id; Query OK, 0 rows affected (0.14 sec)
查看触发器命令
show triggers
这个命令只能看到都有哪些的triggers，而看不到trigger的具体信息。
所有触发器信息都存储在information_schema数据库下的triggers表中,可以使用SELECT语句查询。如果有很多个触发器，最好通过TRIGGER_NAME字段指定查询某一个触发器。
例如：
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME=’XXX’;
限制条件
条件限制对一些涉及到金额的场合（如开篇提到的）非常重要，在电商的限购数额中也会有应用。
trigger利用delimiter,begin和if语句块实现限制条件。
例如： mysql> delimiter // mysql> create trigger shop_limit before update on shoppingcar -> for each row -> begin -> if new.amount>3 then -> set new.amount=3; -> elseif new.amount<0 then -> set new.amount=0; -> end if; -> end; // mysql> delimiter ;
条件语句程序块用begin和end包裹起来实现
delimiter：切换结束符，因为；是MySQL中默认的结束符，如果程序块中出现；符号，就会引起冲突。最后要将结束符修改回来。注意delimiter与结束符之间有空格，否则会无法切换。
更新数据前：
更新数据：
mysql> update shoppingcar set amount=4 where u_id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
更新数据后：
购买量无法超过3
关于触发器与事务
对于事务表（Innodb），before语句后面的判断语句失败将会导致回滚事件语句执行的所有更改。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务性表（MyISAM），无法执行此类回滚，因此尽管语句失败，但在错误点之前执行的任何更改仍然有效。
关于触发器的使用限制
触发器执有一些限制：
1. 触发器不能使用CALL 语句来将数据返回给客户端或使用动态SQL的存储过程。但允许存储过程通过OUT或INOUT 参数将数据返回到触发器 。
2. 触发不能使用事务相关的语句，如 START TRANSACTION，COMMIT或ROLLBACK。因为触发器对update,delete,insert等事件做了处理，并且是按照before，SQL语句，after的顺序来执行的，一旦某一步出错，就会回滚数据。如果在触发器中使用事务，就会产生矛盾。
参考文章：
https://juejin.im/post/58819729b123db75e08eeae1