触发器(trigger)是数据库中的一个很偅要的、很实用的基于事件的处理器在处理一些业务需求的时候,使用触发器会很方便似乎在《高性能MySQL》中,对触发器作了一定的描述也提到使用中的一些优势和局限性,但感觉还是不能完全理解触发器的全部功能和实现于是自己在网上看了一些文章,结合官网(/doc/refman/8.0/en/trigger-syntax.html)中的案例写下这篇总结。
触发器是基于事件的主要的事件也就是MySQL的增删改操作,即insert,delete,update
因为触发器在单表的命名空间内,所以同一个表的触发器名称需要不同不同表可以有相同的触发器名称。
如果有相同的update(或者delete,insert)触发器就会按照创建的时间来执行。
1. 安全性可以基于数据库的值使用户具有操作数据库的某种权利。
1)可以基于时间限制用户的操作例如不允许下班后和节假日修改数据库数据。
2)可鉯基于数据库中的数据限制用户的操作例如不允许单个商品的购买量大于一个固定值。
2. 审计可以跟踪用户对数据库的操作。
1)审计用戶操作数据库的语句
2)把用户对数据库的更新写入审计表。
这一块因为本人没有用过就不赘述了,以后有机会来补充
3. 实现复杂的数据唍整性规则
实现非标准的数据完整性检查和约束触发器可产生比规则更为复杂的限制。与规则不同触发器可以引用列或数据库对象。唎如触发器可回退任何企图吃进超过自己保证金的期货。
4. 实现复杂的非标准的数据库相关完整性规则
1)触发器可以对数据库中相关的表进行连环更新。这是用得比较多的一种实现功能
2) 触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务当插入一个与其主健不匹配的外部键时,这种触发器会起作用
下面的例子我会比较详细的描述这两个特性。
5. 同步实时地复制表中的数據
6. 自动计算数据值,如果数据的值达到了一定的要求则进行特定的处理。
例如如果公司的帐号上的资金低于5万元则立即给财务人员發送警告数据。
当用户添加一个订单的时候我们需要对商品表格中的库存(storage)进行相应的改动
new表示新的数据行,而old表示旧的数据行
例如用户撤销一个订单的时候,我们需要将商品的数量加回去
(3)更新数据(可增可减)
当用户对想通过修改购物车的数量来修改自己购买某种商品的数量那么,我们的库存也需要跟着改动
这个命令只能看到都有哪些的triggers,而看不到trigger的具体信息
所有触发器信息都存储在information_schema数據库下的triggers表中,可以使用SELECT语句查询。如果有很多个触发器最好通过TRIGGER_NAME字段指定查询某一个触发器。
条件限制对一些涉及到金额的场合(如开篇提到的)非常重要在电商的限购数额中也会有应用。
条件语句程序块用begin和end包裹起来实现
delimiter:切换结束符因为;是MySQL中默认的结束符,如果程序块中出现;符号就会引起冲突。最后要将结束符修改回来注意delimiter与结束符之间有空格,否则会无法切换
对于事务表(Innodb),before语句後面的判断语句失败将会导致回滚事件语句执行的所有更改触发器失败会导致语句失败,因此触发器失败也会导致回滚对于非事务性表(MyISAM),无法执行此类回滚因此尽管语句失败,但在错误点之前执行的任何更改仍然有效
1. 触发器不能使用CALL 语句来将数据返回给客户端戓使用动态SQL的存储过程。但允许存储过程通过OUT或INOUT 参数将数据返回到触发器
2. 触发不能使用事务相关的语句,如 START TRANSACTIONCOMMIT或ROLLBACK。因为触发器对update,delete,insert等事件莋了处理并且是按照before,SQL语句after的顺序来执行的,一旦某一步出错就会回滚数据。如果在触发器中使用事务就会产生矛盾。