触发器

触发器

触发器是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

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了659.4k个字了!

Your browser is out-of-date!

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

×