触发器在MySQL数据库中的应用

摘 要：触发器能够解决数据库复杂的业务规则或要求，从而保证数据库的完整性、正确性和一致性。本文介绍了触发器的概念、用途、创建、使用等，旨在帮助数据库编程人员正确使用触发器。

关键词：触发器；trigger；MySQL

中图分类号：TP309

1 触发器的概念

触发器是一个被指定关联到一个表的数据库对象，当对一个表的特定事件出现时，它将被激活。触发器可以看作是特殊的存储过程，不同的是，执行存储过程要使用call语句来调用，只要当一个预定义的事件发生的时候，触发主体动作就会被MySQL自动调用。具体而言，触发器就是MySQL响应insert、update和delete语句而自动执行的一条或一组MySQL语句。

2 触发器的用途

（1）它可用于检查插入到表中的值。例如：每当增加一个学生到数据库的学生基本信息表时，都检查其电话号码的格式是否正确。（2）它能对更新涉及的值进行计算。例如：每当客户订购一个产品时，都从产品库存表中将库存量减去订购的数量。（3）用于创建审计跟踪，也就是可使用触发器把表的更改状态以及之前和之后的状态记录到另外一张数据表中。正确的使用触发器，这能保障数据库中数据的完整性正确性，以及多个表之间数据的一致性。

3 触发器的创建

语法格式：Create [DEFINER={user|CURRENT_USER}] TRIGGER t_name t_time t_event ON tbl_name FOR EACH ROW t_body

其说明如下：DEFINER从句：指定当触发器被触发时满足事件执行权限的安全上下文，为可选项。t_name：触发器的名称，触发器在当前数据库中必须具有唯一的名称。t_time：触发器被触发的时机，有before和after两个选项。t_event：触发事件，包括insert、update和delete，指定激活触发器的语句种类。tbl_name：标识建立触发器的表名。FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器。t_body：指定触发器被触发后执行的语句，称为触发器主体。当触发器主体有多条语句时，将要执行的多条语句放begin和end語句之间，各语句之间要用分隔符“；”隔开。

4 触发器的查看

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。查看当前数据库中的触发器语句为：“show TRIGGERS”，但执行该语句显示的触发器信息较为混乱，可以在show TRIGGERS命令后加“＼G”，这样显示的信息就相对更有条理。在MySQL数据库中，所有的触发器定义都存放在系统自带的数据库information_schema的triggers表中，所以可以通过语句“select * from information_schema.triggers where trigger_name=触发器名称”查看满足指定条件的触发器，该方法更为实用。

5 触发器的使用

触发器是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。根据触发的事件，触发器可分为insert、delete和update触发器。

5.1 insert触发器。insert触发器可在insert语句执行之前或之后执行。需要注意以下几点：在insert触发器代码内，可引用一个名为new的虚拟表，来访问被插入的行。在before insert触发器中，new中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。对于auto_increment列，new在insert执行之前包含的是0值，在insert执行之后将包含新的自动生成值。

5.2 delete触发器。delete触发器可在delete语句执行之前或之后执行。在delete触发器代码内，可以引用一个名为old的虚拟表，来访问被删除的行。但old中的值全部是只读的，不能被更新。

5.3 update触发器。相比insert触发器和delete触发器，update触发器既可使用new虚拟表，也可以使用old虚拟表。new虚拟表可以访问更新后的值，而old虚拟表可以访问（update语句执行前）的值。同样，old中的值全部是只读的，不能被更新。特别要注意的是，当触发器涉及对触发表自身的更新操作时，只能使用before update触发器，而after update触发器将不被允许。

5.4 触发器应用举例。某进销存数据库（db_jxc）中有业务统计表（persons）和销售额表（sales）等表。表person有销售人姓名、销售数量字段。表sales有姓名、销售金额字段。要求每更新一次person表后，都要更新sales表对应的销售金额字段（销售金额=销售数量*20）。

经分析，可用触发器来实现，操作步骤如下：（1）创建数据库：create database db_jxc；（2）选择数据库：use db_jxc；（3）创建表：Create table persons（name char（10），nun int）；Create table sales（name char（10），sum int）；（4）创建触发器：Create TRIGGER nun_sum after insert on persons FOR EACH ROW INSERT into sales values（new.name，20*new.nun）；（5）向表persons中插入记录：Insert into persons values（‘jack，50），（‘luck，47）；（6）检查触发器的执行情况，如图1所示。

6 触发器的删除

删除触发器指删除原来已经在某个数据库中创建的触发器，与MySQL中删除数据库的命令相似。

格式：drop TRIGGER [if exists] [schema_name.]trigger_name；

If exists为可选项，用于避免删除没有的数据库。可选项schema_name用于指定触发器所在的数据库的名称，若没有指定，则默认为当前数据库。trigger_name指定要删除的触发器的名称。当删除一个表的同时，也会自动地删除该表上的触发器。

另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。在应用完触发器后，切记一定要将触发器删除，否则在执行某些数据库操作时，会造成数据的变化。

7 使用触发器的注意事项

在使用触发器的时候需要注意：（1）在触发器的创建过程中，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器，即insert、update和delete的之前与之后。（2）及时删除不再需要的触发器。触发器定义之后，每次执行触发事件，都会激活触发器并执行触发的主体动作。如果需求发生变化，而触发器没有进行相应的改变或删除，则触发器仍然会执行旧的语句，从而影响新的数据的完整性。

8 结束语

本文介绍了触发器的用途、创建、使用方法等，案例代码经多次调试运行无错误。触发器能有效地保护数据库中的数据，能解决一些复杂的问题。正确使用触发器是MySQL数据库编程人员必备的素质。

参考文献：

[1]MySQL数据库程序设计[M].北京：高等教育出版社，2013.

[2]刘增杰，张少军.MySQL5.5从零开始学[M].北京：清华大学出版社，2012.

作者简介：徐安令（1979-），云南宣威人，讲师，计算机应用技术工程硕士，研究方向：数据库应用与程序设计。

作者单位：德宏师范高等专科学校 计科系，云南德宏 678400