触发器

触发器

触发器是 MySQL 5.0 开始支持的一种过程式数据库对象。

触发器

触发器是一个被指定关联到一个表的数据库对象,但对一个表的特定事件出现时,它将会被激活。触发器具有 MySQL 语句在需要时才被执行的特点,即某条(或某些)MySQL 语句在特定事件发生时自动执行。例如:

  1. 每当增加一个客户到数据库的客户基本信息表时,都检查其电话号码的格式是否正确;
  2. 每当客户订购一个产品时,都从产品库存中减去订购的数量。
  3. 每当删除客户基本信息表中一个客户的全部信息数据时,该客户所订购的未完成订单信息也应该自动被删除。
  4. 无论何时删除一行,都在数据库的存档中保留一个副本。

触发器与表的关系非常密切,用于保护表中的数据。当有操作影响到触发器所保护的数据时,触发器就会被自动执行,从而保障数据库中数据的完整性,以及多个表之间数据的一致性。
具体而言,触发器就是 MySQL 响应 insert、update 和 delete 语句自动执行的一条 MySQL 语句(或位于 begin 和 end 语句之间的一组 MySQL 语句)。需要注意的是,其他 MySQL 语句是不支持触发器的。

创建触发器

在 MySQL5.5 中,可以使用 create trigger 语句创建触发器,其语法格式为:

1
2
3
create
trigger trigger_name trigger_time trigger_event
on tbl_name for each row trigger_body

语法说明如下:

  1. trigger_name:触发器的名称,触发器在当前数据库中必须具有唯一的名称。如果要在某个特定的数据库中创建,名称前面应该加上数据库的名称。
  2. trigger_time:触发器被触发的时刻,它有两个选项,即 before 和 after,用于表示触发器是在激活它的语句之前或者之后激发。如果希望验证新数据是否满足使用的限制,则使用 before;如果希望在激活触发器的语句执行之后完成几个或更多的改变,通常使用 after 选项。
  3. trigger_event:触发事件,用于指定激活触发器的语句的种类,其可以是下述值之一:
    • insert:将新行插入表时激活触发器。例如,insert 的 before 触发器不仅能被 MySQL 的 insert 语句激活,也能被 load data 语句激活。
    • update:更改表中的某一行时激活触发器。
    • delete:删除某一行时激活触发器。
  4. tbl_name:与触发器关联的表名,必须是永久性表,不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如,对于一张数据表,不能同时拥有两个 before update 触发器,但是可以有一个 before update 触发器和一个 before insert 触发器,或一个 before update 触发器与一个 alter update 触发器。
  5. for each row:这个声明用来指定对于受触发事件影响的每一行都要激活触发器的动作。例如,使用一条 insert 语句向一个表中插入多行数据时,触发器会对每一行数据的插入都执行相应的触发操作。
  6. trigger_body:触发器动作主体,包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句,可使用 begin…end 复合语句结构。这里可使用存储过程中允许的相同语句。

注意,在触发器的创建中,每个表每个事件每次只允许一个触发器。因此,每个表最多支持六个触发器,即每条 insert、update 和 delete 的前后。单一触发器不能与多个事件或多个表关联,例如,需要一个对 insert 和 update 操作执行的触发器,则应该定义两个触发器。

另外,在 MySQL 中,如果需要查看数据库中已有的触发器,可以使用 show triggers 语句,其语法格式为:

1
show triggers [{from | in} db_names];

例如,在数据库 db_school 的表 tb_student 中创建一个触发器 tb_student_insert_trigger,用于每次向表 tb_student 中插入一行数据时将学生变量 str 的值设置为“one student added!”:

1
2
3
4
5
6
7
8
9
10
11
12
create trigger db_school.tb_student_insert_trigger
after insert
on db_school.tb_student
for each row
set @str = '一个学生被添加!';

# 添加一个学生
insert into db_school.tb_student
values
('2013120202', '张大勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
# 验证触发器
select @str;

删除触发器

drop trigger 需要 super 权限。当删除一个表的同时也会自动地删除这个表上的触发器。另外触发器不能更新或覆盖,为了修改一个触发器,必须先删除再重新创建。

1
drop trigger if exists db_school.tb_student_insert_trigger;

使用触发器

实际使用中,MySQL 所支持的触发器有三种:insert 触发器、delete 触发器和 update 触发器。

insert 触发器

使用该触发器时需要注意以下几点:

  • 在 insert 触发器代码内可以引用一个名为 new 的虚拟表来访问插入的行;
  • 在 before insert 触发器中,new 中的值可以被更新,即允许更改被插入的值(只要具有对应的操作权限)。
  • 对于 auto_increment 列,new 在 insert 执行之前包含的是 0 值,在 insert 执行之后将包含新的自动生成值。

例如,重新创建 tb_student_insert_trigger,用于每次向表 tb_student 中插入一条数据时将学生变了 str 的值设置为新插入学生的学号:

1
2
3
4
5
6
7
8
9
10
11
12
13
create trigger db_school.tb_student_insert_trigger
after insert
on db_school.tb_student
for each row
set @str = new.studentNo;

# 然后插入一行数据
insert into db_school.tb_student
values
('2013330101', '张三勇', '男', '1997-12-11', '山西', '汉', 'AC1301');

# 验证触发器
select @str;

delete 触发器

delete 触发器代码内可以引用一个名为 old 的虚拟表来访问被删除的行。old 中的值全部是只读的,不能被更新。

update 触发器

update 触发器既可以引用一个名为 old 的虚拟表访问之前的值,还可以引用一个名为 new 的虚拟表访问更新的值。

在 before update 触发器中,new 中的值可能被更新,即允许更改将要用于 upadate 语句中的值。

当触发器涉及对表自身的更新操作时,只能使用 before update 触发器,而 alter update 触发器将不被允许。

例如,在 db_school.tb_student 中创建以恶搞 tb_student_update_trigger,用于每次更新表时将该表中 nation 列的值设置为 native 列的值。

1
2
3
4
5
6
7
8
9
10
11
12
create trigger db_school.tb_student_update_trigger
before update
on db_school.tb_student
for each row
set new.nation = old.native;

# 更新一个数据
update db_school.tb_student set nation = '壮'
where studentName = '张三勇';
# 执行下面的语句会发现,nation列的值为"山西"而非壮
select nation from db_school.tb_student
where studentName = '张三勇';

在触发器的执行过程中,MySQL 会安装下面的方式来处理错误:

  1. 如果 before 触发程序失败,则 MySQL 将不执行相应行上的操作;
  2. 仅当 before 触发程序和行操作都被成功执行,MySQL 才会执行 after 触发程序(如果有的话);
  3. 如果在 before 或 after 触发程序的执行过程中出现错误,将导致调用触发程序的整个语句的失败。

对触发器的进一步说明

  1. 与其他 DBMS 相比,目前 MySQL 版本支持的触发器还较为低级;
  2. 创建触发器可能需要特殊的安全访问权限,但是触发器的执行是自动的。
  3. 应该多用触发器来保证数据的一致性、完整性和正确性。例如,使用 before 触发程序进行数据的验证和净化,这样保证插入表的数据是所需要的正确数据,而且这种操作对用户是透明的。
    触发器是一种非常有意义的使用模式——创建审计跟踪,也就是可以使用触发器把表的更改状态以及之前和之后的状态记录到另一张数据表中。
# MySQL

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×