MySQL数据库从入门到精通—触发器

七月阳光

触发器概述
触发器的概念及优点
前面学习了MySQL的存储过程（MySQL数据库从入门到精通—存储过程），在MySQL中还有一种类似的存在——触发器，它的执行不是由程序调用，也不是手动开启，而是由事件来触发。当对某个表进行操作时会自动激活并执行触发器，例如对一个表进行INSERT、DELETE、UPDATE等操作时会激活并执行触发器。
触发器是用户定义在关系表上的一类由事件触发的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。触发器类似于约束，但是比约束灵活，具有更强大的数据控制能力。
触发器的优点如下。
（1） 自动执行：触发器在操作表数据时立即被激活。
（2） 级联更新：触发器可以通过数据库中的相关表进行层叠更改。
（3） 强化约束：触发器可以引用其他表中的列，能够实现比CHECK约束更复杂的约束。
（4） 跟踪变化：触发器可以阻止数据库中未经许可的指定更新和变化。
（5） 强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。
触发器的作用
触发器是基于行触发的，删除、新增或者修改操作可能都会激活触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，因此在设计触发器的时候一定要有所考虑，尽量不要编写过于复杂的触发器，也不要增加过多的触发器。
触发器是一种特殊的存储过程，它在插入、删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细、更复杂的数据控制能力。触发器主要有6个作用，具体如下。
（1） 安全性：可以基于数据库使用户具有操作数据库的某种权利，可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据，还可以基于数据库中的数据限制用户的操作，例如不允许某个用户做修改操作。
（2） 审计：可以跟踪用户对数据库的操作，审计用户操作数据库的语句，把用户对数据库的更新写入审计表。
（3） 实现复杂的数据完整性规则，实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象，还可以提供可变的默认值。
（4） 实现复杂的非标准的数据库相关完整性规则：触发器可以对数据库中相关表进行连环更新。
（5） 同步实时地复制表中的数据。
（6） 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

触发器的操作
详细阐述了触发器的基本概念，接下来讲解触发器的操作，包括创建触发器、查看触发器、使用触发器和删除触发器。
数据准备
在讲解触发器之前需要先创建两张数据表（测试表test1和测试表test2）用于后面的例题演示，其中测试表test1的表结构如表10.1所示。
表10.1 test1表

字段	字段类型	说明
id	INT	编号
name	VARCHAR(50)	姓名

在表10.1中列出了测试表test1的字段、字段类型和说明。然后创建测试表test1。


测试表test2的表结构如表10.2所示。
表10.2 test2表

字段	字段类型	说明
id	int	编号
name	varchar(50)	姓名

在表10.2中列出了测试表test2的字段、字段类型和说明。然后创建测试表test2。


至此两张表创建完成，本章后面的演示例题会用到这两张表。
创建触发器
在MySQL中创建触发器的语法格式如下。


在以上语法格式中，trigger_name表示触发器的名称，由用户自行指定；trigger_time表示触发时机，取值为BEFORE或AFTER；trigger_event表示触发事件，取值为 INSERT、UPDATE或DELETE；tbl_name表示建立触发器的表名，即在哪张表上建立触发器；trigger_stmt表示触发器程序体，可以是一条SQL语句，也可以是BEGIN和END包含的多条语句。由此可以看出一共可以创建6种触发器，即BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE和AFTER DELETE。此外用户还需注意，不能同时在一个表上建立两个相同类型的触发器，因此在一个表上最多可以建立6个触发器。
MySQL除了对INSERT、UPDATE、DELETE基本操作进行定义以外，还定义了LOAD DATA和REPLACE语句，这两种语句也能引起上述6种类型触发器的触发。LOAD DATA语句用于将一个文件装入到一个数据表中，相当于一系列的INSERT操作。REPLACE 语句与INSERT语句类似，只是当在表中有PRIMARY KEY或UNIQUE索引时，若插入的数据和原来 PRIMARY KEY或UNIQUE索引一致时，会先删除原来的数据，然后增加一条新数据，可以理解为一条REPLACE语句有时候等价于一条INSERT语句，有时候等价于一条DELETE语句加上一条INSERT语句。各种触发器的激活和触发时机具体如下所示。
l INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA或REPLACE语句触发。
l UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发。
l DELETE型触发器：删除某一行时激活触发器，可能通过DELETE和REPLACE语句触发。
创建触发器t_afterinsert_on_test1用于向测试表test1添加记录后自动将记录备份到测试表test2中。


以上执行结果证明触发器创建完成。
接下来测试触发器的使用，首先向测试表test1中插入一条数据。


以上执行结果证明数据插入完成。使用SELECT语句查看表中数据。


以上执行结果可以看出数据插入完成。然后查看test2表中的数据。


从以上执行结果可以看出，测试表test2自动备份了向测试表test1中插入的数据。这是因为在进行INSERT操作时激活了触发器t_afterinsert_on_test1触发器自动向测试表test2中插入了同样的数据。
在上面的示例中使用了NEW关键字，在MySQL中定义了NEW和OLD用来表示触发器的所在表中触发了触发器的哪一行数据，NEW和OLD的具体用法如下。
在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据。
在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据。
在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据。

NEW关键字的使用语法格式如下所示。


在以上语法格式中，columnName表示相应数据表的某个列名，OLD关键字也是类似的使用方法。值得注意的是，OLD是只读的，而NEW可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用。
创建触发器t_afterdelete_on_test1，用于删除测试表test1记录后自动将测试表test2中的对应记录删除。


以上执行结果证明触发器创建完成。
接下来测试触发器的使用，首先删除测试表test1中id为1的数据。


以上执行结果证明数据删除完成。查看测试表test1中的数据。


从以上执行结果可以看出数据删除完成。然后查看测试表test2中的数据。


从以上执行结果可以看出，测试表test2中的记录同样被删除，这是因为在进行DELETE操作时激活了t_afterdelete_on_test1触发器，触发器自动删除了测试表test2中对应的记录。

查看触发器
查看触发器有两种方式，接下来对这两种方式分别讲解。
1.使用SHOW TRIGGERS语句查看触发器
使用SHOW TRIGGERS语句可以查看触发器，具体语法格式如下。


2.从information_schema.triggers表中查看触发器
在MySQL中，触发器的信息存储在information_schema库下的triggers表中，用户可以通过查询该表的数据来查询触发器的信息，并且可以查询指定触发器的指定信息。
使用触发器
在使用触发器时有以下几个注意事项。
（1） 触发器程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储过程通过参数将数据返回触发器程序。也就是存储过程通过OUT或INOUT类型的参数可以将数据返回触发器，但不能调用直接返回数据的过程。
（2） 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，例如START TRANS-ACTION、COMMIT或ROLLBACK。
（3） MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作。如果是对事务表进行操作，若出现错误，那么将会被回滚；如果是对非事务表进行操作，那么就无法回滚，数据可能会出错。
删除触发器
在删除触发器时，当前用户必须具有删除触发器的权限。使用DROP TRIGGER语句可以删除触发器，具体语法格式如下。


在以上语法格式中，trigger_name表示需要删除的触发器名称；IF EXISTS是可选的，表示如果触发器不存在，不发生错误，而是产生一个警告。
将触发器t_afterdelete_on_test1删除。


以上执行结果证明存储过程删除成功。
然后使用SHOW TRIGGERS语句查看数据库中所有的触发器。


从以上执行结果可以看出，此时数据库中只有一个触发器t_afterinsert_on_test1，可见通过DROP TRIGGER语句成功删除了触发器t_afterdelete_on_test1。
小案例
详细讲解了MySQL中触发器的使用，接下来通过一个小案例演示如何使用触发器更方便地实现数据完整性约束。
该案例中有两张表，它们通过外键关联，当删除主表中的记录时，从表中对应的记录就没有意义了。使用触发器自动将没有意义的数据删除，这样就可以实现数据的完整性约束。
在演示案例之前需要先创建两张关联表（学生表student和交换生表bor_student，表之间通过外键stu_id关联），其中学生表student的表结构如表10.3所示。
表10.3 student表

字段	字段类型	说明
stu_id	INT	学生编号
stu_name	VARCHAR(30)	学生姓名
stu_sex	ENUM(‘m’,’f’)	学生性别

在表10.3中列出了student表的字段、字段类型和说明。然后创建student表。


在创建完成student表后向表中插入数据。


以上执行结果证明数据插入完成。然后查看表中的数据。


交换生表bor_student的表结构如表10.4所示。
表10.4 bor_student表

字段	字段类型	说明
bor_id	int	交换编号
stu_id	int	学生编号
bor_date	date	交换日期
ret_date	date	返回日期

在表10.4中列出了bor_student表的字段、字段类型和说明。然后创建bor_student表。


在创建完成bor_student表后向表中插入数据。


以上执行结果证明数据插入完成。然后查看表中数据。
接下来编写触发器t_beforedelete，当student表中的记录删除时自动删除bor_student表中对应的数据。


删除学生表中stu_id为3的记录。


以上执行结果证明student表中stu_id为3的记录被成功删除。然后查看student表中的数据进行验证。


在student表中的记录被删除后，查看bor_student表中对应的记录是否存在。


从以上执行结果可以看出，bor_student表中stu_id为3的记录被自动删除，这就是触发器在自动维护数据完整性。

小结：MySQL数据库从入门到精通—触发器
介绍了触发器的相关内容，首先讲解了触发器的概念，接着讲解了触发器的相关操作，包括触发器的创建、查看、使用和删除等。大家需要通过动手实践去熟练掌握触发器的操作。

谢忠月

介是神马？！！