触发器 (trigger) 是数据库中的一个很重要的, 很实用的基于事件的处理器, 在处理一些业务需求的时候, 使用触发器会很方便. 似乎在《高性能 MySQL》中, 对触发器作了一定的描述, 也提到使用中的一些优势和局限性, 但感觉还是不能完全理解触发器的全部功能和实现. 于是自己在网上看了一些文章, 结合官网 () 中的案例, 写下这篇总结.
基本理解:
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 PRECEDESins_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';
(4)限制条件
条件限制对一些涉及到金额的场合 (如开篇提到的) 非常重要, 在电商的限购数额中也会有应用.
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 的顺序来执行的, 一旦某一步出错, 就会回滚数据. 如果在触发器中使用事务, 就会产生矛盾.
来源: http://www.linuxidc.com/Linux/2020-04/162980.htm