良许Linux教程网 干货合集 MySQL触发器深入讲解

MySQL触发器深入讲解

触发器(trigger):监视某种情况,并触发某种操作,它是提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,例如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

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 PRECEDES ins_sum

SET

@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),

@withdrawals = @withdrawals + IF(NEW.amount

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)

结果:

img

**(**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)
img

查看触发器命令

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.amountthen
   ->      set new.amount=0;
   ->   end if;
   -> end; //
mysql> delimiter ;

条件语句程序块用begin和end包裹起来实现

delimiter:切换结束符,因为;是MySQL中默认的结束符,如果程序块中出现;符号,就会引起冲突。最后要将结束符修改回来。注意delimiter与结束符之间有空格,否则会无法切换。

更新数据前:

img

更新数据:

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

更新数据后:

img

购买量无法超过3

关于触发器与事务

对于事务表(Innodb),before语句后面的判断语句失败将会导致回滚事件语句执行的所有更改。触发器失败会导致语句失败,因此触发器失败也会导致回滚。对于非事务性表(MyISAM),无法执行此类回滚,因此尽管语句失败,但在错误点之前执行的任何更改仍然有效。

关于触发器的使用限制

触发器执有一些限制:

\1. 触发器不能使用CALL 语句来将数据返回给客户端或使用动态SQL的存储过程。但允许存储过程通过OUT或INOUT 参数将数据返回到触发器 。

\2. 触发不能使用事务相关的语句,如 START TRANSACTION,COMMIT或ROLLBACK。因为触发器对update,delete,insert等事件做了处理,并且是按照before,SQL语句,after的顺序来执行的,一旦某一步出错,就会回滚数据。如果在触发器中使用事务,就会产生矛盾。

以上就是良许教程网为各位朋友分享的Linu系统相关内容。想要了解更多Linux相关知识记得关注公众号“良许Linux”,或扫描下方二维码进行关注,更多干货等着你 !

img
本文由 良许Linux教程网 发布,可自由转载、引用,但需署名作者且注明文章出处。如转载至微信公众号,请在文末添加作者公众号二维码。
良许

作者: 良许

良许,世界500强企业Linux开发工程师,公众号【良许Linux】的作者,全网拥有超30W粉丝。个人标签:创业者,CSDN学院讲师,副业达人,流量玩家,摄影爱好者。
上一篇
下一篇

发表评论

联系我们

联系我们

公众号:良许Linux

在线咨询: QQ交谈

邮箱: yychuyu@163.com

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部