MySQL触发器深入讲解

发布时间：2022-12-15 11:08:58 所属栏目：MySql教程来源：

导读：　　触发器（trigger）：监视某种情况，并触发某种操作mysql触发器，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是

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

　　基本理解：\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)
　　结果：

　　**（**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.amountthen
　　???->??????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的顺序来执行的，一旦某一步出错，就会回滚数据。如果在触发器中使用事务，就会产生矛盾。

（编辑：云计算网_梅州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

mysql的varchar与text	MySQL之两个存储引擎
具体配置信息	mysql中clob和blob的差