注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL触发器(trigger)

MySQL 彭东稳 8640次浏览 已收录 0个评论

一、触发器介绍

MySQL的触发器和存储过程一样,都是嵌入到MySQL的一段程序,触发器是个特殊的存储过程。它们不同的是执行存储过程要使用CALL语句来调用,而触发器是由事件来触发某个操作,这些事件包括INSERT、UPDATE和DELETE语句。如果定义了触发程序,当对表执行INSERT、DELETE或UPDATE语句时,将激活触发程序。可以将触发程序设计为在执行语句之前或之后激活。例如,可以在从表中删除每一行之前,或在更新每一行之后激活触发程序。

二、创建触发器

触发器可以查询其他表,而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如:可以根据客户当前的账户状态,控制是否允许插入新订单。

创建只有一个执行语句的触发器,语法如下:

trigger_name – 标识触发器名称,用户自行指定;

trigger_time – 标识触发时机,可以指定为before或after;

trigger_event – 标识触发事件,包括INSERT、UPDATE和DELETE;

tbl_name – 标识建立触发器的表名,即在哪张表上建立触发器;

trigger_body – 是触发器程序体,触发器程序可以使用begin和end作为一个事务,中间包含多条语句。

示例:创建单个执行语句的触发器

首先创建一个account表,表中有两个字段,分别为:acct_num字段(定义为int类型),amount字段(定义为浮点类型)。

其次创建一个名为tri_amount的INSERT触发器,触发的条件是向account表更新数据之后,对新插入的amount字段值进行求和计算。

代码如下:

然后往表中插入数据进行测试:

可以看到@sum变量会随着amount字段插入的数值一直累加。这里我们使用的“NEW.amount”表示更新以后的列值,如果使用“OLD.amount”表示更新以前的列值,属于固定语法。

再来创建一个UPDATE类型的触发器:

创建多个执行语句的触发器

上面的代码创建了一个名为tri_test的触发器,这个触发器的触发条件是在向表test1插入数据前执行触发器的语句,具体执行的代码如下:

下面看一下四个表的变化

执行结果显示,在向表test1插入记录的时候,test2、test3、test4都发生了变化。从这个例子看INSERT触发了触发器,向test2中插入了test1中的值,删除了test3中相同的内容,同时更新了test4的b4,即与插入的值相同的个数。

如果你使用OSC工具做过Online DDL操作,就会知道这个工具的原理就是利用INSERT、UPDATE、DELETE三个触发器来完成的。具体触发器如下,可以做做测试。

就拿这三个触发器来说,第一个触发器作用就是当“online_table”表有删除操作时,就会对应使用DELETE IGNORE语句删除“_online_table_new”表的记录,如果“_online_table_new”表没有这条记录则忽略。第二个触发器作用就是当“online_table”表有更新操作时,就会对应使用REPLACE INTO语句更新“_online_table_new”表的记录,存在就更新,不存在就插入。第三个触发器作用就是当“online_table”表有插入操作时,就会对应使用REPLACE INTO语句更新“_online_table_new”表的记录,存在就更新,不存在就插入。具体OSC工具是如何运用这三个工具的,可以参考“pt-online-schema-change在线修改表结构”。

三、管理触发器

  • 查看触发器

通过SHOW TRIGGERS查看触发器的语句如下:

在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中,可以通过查询命令SELECT来查看,具体的语法如下:

  • 删除触发器

使用DROP TRIGGER语句可以删除MySQL中以及定义的触发器,删除触发器语句基本语句格式如下:

四、总结

  • 触发器对性能有损耗,应当非常谨慎使用。
  • 对于事务表,触发器执行失败则整个语句回滚,保证一致性。
  • 对于ROW格式主从复制,触发器不会在从库上执行。
  • 使用触发器时应防止递归执行,导致出现死循环,耗尽CPU资源。
  • 在使用触发器的时候需要注意,对于相同的表,相同的时间只能创建一种触发器类型。比如,对表account创建了一个before insert触发器,那么如果对表account再次创建一个before insert触发器,MySQL将会报错。此时,只可以在表account上创建after insert或者before update类型的触发器。

灵活的运用触发器将为操作省去很多麻烦。另外对于无用的触发器及时删除。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)or分享 (0)
关于作者:

您必须 登录 才能发表评论!