MYSQL学习与数据库综合实验（九）——触发器

9触发器

9.1触发器是什么

MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，而是通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（INSERT，DELETE 或 UPDATE）时就会激活它执行。

触发器与数据表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性。

在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器，其它 SQL 语句则不会激活触发器。

那么为什么要使用触发器呢？比如，在实际开发项目时，我们经常会遇到以下情况：

• 在学生表中添加一条关于学生的记录时，学生的总数就必须同时改变。
• 增加一条学生记录时，需要检查年龄是否符合范围要求。
• 删除一条学生信息时，需要删除其成绩表上的对应记录。
• 删除一条数据时，需要在数据库存档表中保留一个备份副本。

虽然上述情况实现的业务逻辑不同，但是它们都需要在数据表发生更改时，自动进行一些处理。这时就可以使用触发器处理。例如，对于第一种情况，可以创建一个触发器对象，每当添加一条学生记录时，就执行一次计算学生总数的操作，这样就可以保证每次添加一条学生记录后，学生总数和学生记录数是一致的。

9.2创建基本语法

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。

语法格式如下：

CREATE  trigger <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

语法说明如下。

1) 触发器名

触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。

2) INSERT | UPDATE | DELETE

触发事件，用于指定激活触发器的语句的种类。

注意：三种触发器的执行时间如下。

• INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
• DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
• UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。

3) BEFORE | AFTER

BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

4) 表名

与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

5) 触发器主体

触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。

6) FOR EACH ROW

一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGERS 语句。

9.3删除触发器基本语法

与其他 MySQL 数据库对象一样，可以使用 DROP 语句将触发器从数据库中删除。

语法格式如下：

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

语法说明如下：

1) 触发器名

要删除的触发器名称。

2) 数据库名

可选项。指定触发器所在的数据库的名称。若没有指定，则为当前默认的数据库。

3) 权限

执行 DROP TRIGGER 语句需要 SUPER 权限。

4) IF EXISTS

可选项。避免在没有触发器的情况下删除触发器。

注意：删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

9.4触发器中关联表中的列

​ 在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：“NEW.column_name”或者“OLD.column_name”。
①NEW.column_name用来引用新行的一列，
②OLD.column_name用来引用更新或删除它之前的已有行的一列。
对于INSERT语句，只有NEW是合法的；
③ 对于DELETE语句，只有OLD才合法；
④而UPDATE语句可以与NEW或OLD同时使用。

9.5delete触发器例子

创建触发器，在employee表中删除员工信息的同时将salary表中该员工的信息删除，以确保数据完整性。创建完后尝试删除employee表中的一行数据，然后查看salary表中的变化情况

delimiter $$
create trigger delete_emp_salaryInfo after delete  on employee for each row begin  delete from salary where salary.employeeID=employee.employeeID; end $$
delimiter ;

9.6update触发器

当修改employee表时，若将employee表中的员工工作时间增加一年，则将收入增加500，增加两年则收入增加1000，以此类推.

delimiter $$
create trigger update_salary_income after update on employee for each row begin update salary set income = income + 500 * (NEW.workYear -OLD.workYear) where NEW.employeeID = OLD.employeeID; end $$

9.7例子

9.7.1创建触发器，在employee表中删除员工信息的同时将salary表中该员工的信息删除，以确保数据完整性。创建完后尝试删除employee表中的一行数据，然后查看salary表中的变化情况

delimiter $$
 create trigger delete_emp_salaryInfo after delete  on employee for each row begin  delete from salary where salary.employeeID=employee.employeeID; end $$
delimiter ;

9.7.2当修改employee表时，若将employee表中的员工工作时间增加一年，则将收入增加500，增加两年则收入增加1000，以此类推

delimiter $$

create trigger update_salary_income after update on employee for each row begin update salary set income = income + 500 * (NEW.workYear -OLD.workYear) where NEW.employeeID = OLD.employeeID; end $$
delimiter ;

可见，当Employee中EmployeeID = 102208的workYear + 2时，对应的salary增加了1000.说明触发器是起了作用了。

9.8详细的实验资料以及指导PPT请访问笔者GitHub地址：https://github.com/LJF2402901363/database_experiment.git

9.9本博客已经同步到个人博客，如有需要请移步：http://moyisuiying.com/index.php/experiment/mysqlexperiment/377.html