深入理解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 | +-------+-------+--------+

关于newold的使用

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)

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/b63c1ad487f63912c8def7465d761f22.html