第十四节：触发器

触发器的简介

show triggers:查看数据库中有哪些触发器
触发器：是与 MySQL 数据表有关的数据库对象，在满足定义条件时触发并执行触发器中定义的语句集合
触发器与表关系密切，主要用于保护表中的数据。特别是当有多个表相互联系的时候，触发器能够让不同的表保持数据的一致性。（一个表的中的数据发生变化，触发器使对应表中的数据发生对应的变化）
注意：在mysql中，只有对相关表执行了insert、update和delete操作时才可能激活触发器
在实际使用中，mysql支持三种触发器：INSERT 触发器、UPDATE 触发器和 DELETE 触发器
触发器的优点：
1. 触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改前/后立即执行触发程序
2. 触发程序可以实施比 foreign key 约束、check 约束更为复杂的检查和操作

INSERT 触发器

在对表执行insert操作之前或之后激活的触发器
注意点：在insert触发器的主体内，可以使用名为new的虚拟表来访问表中的新插入数据（如：new.字段名称）

实例：

create table Student1(id int(11),name varchar(25),age int(11));
create table Student2(id int(11),name varchar(25),age int(11));
create trigger tri_insert after insert on Student1 for each row insert into Student2 values(new.id,new.name,new.age);
insert into Student1 values(1,'Tom',24),(2,'Jack',25);
select * from Student1;
select * from Student2;

UPDATE 触发器

在对表执行 update 操作之前或之后激活的触发器
注意点：
1. 在update触发器的主体内，可以使用名称为new的虚拟表来访问表中更新后的值
2. 在update触发器的主体内，可以使用名称为old的虚拟表来访问表中更新前的值（如：表中age字段对应的值是12，修改之后的值是15。则new.age 的值是12，old.age 的值是15）
3. OLD 中的值全部是只读的，不能被更新（就是不能将新的值赋值给old中字段，但是可以使用old中字段的值）

实例：

create table Student3(id int(11),name varchar(25),age int(11));
create table Student4(id int(11),name varchar(25),age int(11));
insert into Student3 values(1,'Tom',24),(2,'Jack',25);
create trigger tri_update1 after update on Student3 for each row insert into Student4 values(new.id,new.name,new.age); # Studnet4 id为1 age的值为124（更新之后的值）
create trigger tri_update2 after update on Student3 for each row insert into Student4 values(old.id,old.name,old.age); # Student id为2 age的值为25 (更新之前的值)
update Student3 set age = 124 where id = 1;
update Student3 set age = 125 where id = 2;

DELETE 触发器

在对表执行 delete 操作之前或之后激活的触发器
注意点：
1. 在delete触发器主体内，可以使用名为old的虚拟表来访问被删除的值
2. old中的值全部都是只读的，不能被更新

实例：

create table Student5(id int(11),name varchar(25),age int(11));
create table Student6(id int(11),name varchar(25),age int(11));
insert into Student5 values(1,'Tom',24),(2,'Jack',25);
create trigger tri_delete after delete on Student5 for each row insert into Student6 values(old.id,old.name,old.age); # 已经删除的字段可以使用old的虚拟表进行调用
create trigger tri_delete1 after delete on Student5 for each row update Student5 set id = old.id*2; # 创建的时候不会报错，但是在执行delete的时候会报错，old中的值不能够更新
delete from Student5 where id =1;
select * from Student5;
select * from Student6;

创建触发器

语法格式：create trigger 触发器名 before/after insert/update/delete on 表名 for each row 触发器主体
语法说明如下：
1. 触发器名：触发器的名称，触发器名在当前数据库中唯一。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称
2. INSERT | UPDATE | DELETE：触发事件，用于指定激活触发器的语句的类型
  1. INSERT：将数据插入表时激活触发器。INSERT触发器不仅能被 NSERT 语句激活，也能被 LOAD DATA 语句激活
  2. DELETE：从表中删除数据时激活触发器
  3. UPDATE：更改表中的数据时激活触发器
BEFORE | AFTER：是触发器被触发的时刻，表示触发器是在触发事件执行之前触发还是执行之后触发
表名：与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器
触发器主体：触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构
FOR EACH ROW：一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作
注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。

创建 BEFORE 类型触发器：

create table tb_test1(id int(11) primary key,name varchar(25),age int(11));  #创建一张表：
create trigger SumofAge before insert on tb_test1 for each row set @sum = @sum+new.age  #创建一个名为 SumOfAge的触发器，触发的条件是向数据表 tb_test1 中插入数据之前，对新插入的 age 字段值进行求和计算
SET @sum=0;  # 设置用户变量sum为0
insert into tb_test1 values(1,'Tom',24),(2,'Jack',25); # 向表 tb_test1 中插入数据
SELECT @sum; # 定义的 sum 值由 0 变成了49，即插入值 24和 25 的和

创建 AFTER 类型触发器：

create table tb_test2(id int(11) primary key,name varchar(25),age int(11));
create table tb_test3(id int(11) primary key,name varchar(25),age int(11)); # 创建两张表:
create trigger doubleAge after insert on tb_test2 for each row insert into tb_test3 values(new.id,new.name,new.age*2); #创建一个名为 doubleAge的触发器，触发的条件是向数据表 tb_test2 中插入数据，触发器的执行结果是向数据表 tb_test3 中插入相同的数据，并且 age 为 tb_test2 中新插入的 age 字段值的 2 倍。
insert into tb_test2 values(1,'Tom',24),(2,'Jack',25); # 触发器 doubleAge 创建完成之后，向表 tb_test2 中插入记录时，
 select * FROM tb_test2;
 select * FROM tb_test3; #查询的age是tb_test2中age的两倍

修改和删除触发器

修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器
语法格式：DROP TRIGGER [ IF EXISTS ] [数据库名] 触发器名
语法说明如下：
1. IF EXISTS：可选项。避免在没有触发器的情况下删除触发器导致报错
2. 数据库名：可选项。指定触发器所在的数据库的名称。若没有指定数据库名称则表示是当前数据库
3. 触发器名：要删除的触发器名称
4. 权限：执行 DROP TRIGGER 语句需要 SUPER 权限
注意：删除一个表的时候会自动删除表上所有的触发器

实例：删除doubleAge触发器

drop trigger doubleAge; #删除doubleAge触发器
insert into tb_test2 values(3,'Weiking',26); #删除doubleAge后再向tb_test2中插入数据，tb_test3中的数据不再发生变化
select * from tb_test2;
select * from tb_test3;

posted @ 2020-01-03 16:30 WeiKing 阅读(269) 评论(0) 收藏举报

刷新页面返回顶部

WeiKing

第十四节：触发器

触发器的简介

INSERT 触发器

UPDATE 触发器

DELETE 触发器

创建触发器

修改和删除触发器

公告