前言

本篇为我个人学习MySQL时的资料整理笔记

主要参考了李福翠老师在超星上的资料以及上课内容

还有有MySQL5.7从零开始学:视频教学版 / 王英英,李小威编著. -北京:清华大学出版社,2018这本书,以及网上其他博客(具体见文末参考文献)

触发器简介

触发器(trigger)是一个特殊的存储过程,不同的是,执行存储过程要使用call语句来调用,而触发器不需要,并且需要手动启动。触发器主要用于监视某个表的insert、update以及delete等更新操作,这些操作可以分别激活该表的insert、update或者delete类型的触发程序运行,从而实现数据的自动维护。

创建触发器

创建触发器基础语法

使用create trigger语句可以创建一个触发器,

这里就直接用多执行语句的语法了

语法格式如下:

1
2
3
4
5
6
7
8
9
10
delimiter //

create trigger 触发器名 触发时间 触发事件
on 表名 for each row
begin
触发程序
end
//

delimiter ;

触发时间

触发器的触发时间有两种:beforeafter

  • before表示在触发事件发生之前执行触发程序。

  • after表示在触发事件发生之后执行触发器。

触发事件

MySQL的触发事件有三种:

  • insert:将新记录插入表时激活触发程序,例如通过insert、load data和replace语句,可以激活触发程序运行。

  • update:更改某一行记录时激活触发程序,例如通过update语句,可以激活触发程序运行。

  • delete:从表中删除某一行记录时激活触发程序,例如通过delete和replace语句,可以激活触发程序运行。

LOAD DATA 语句用于将一个文件装入到一个数据表中,相当与一系列的 INSERT 操作。

REPLACE 语句一般来说和 INSERT 语句很像,只是在表中有 primary key 或 unique 索引时,如果插入的数据和原来 primary key 或 unique 索引一致时,会先删除原来的数据,然后增加一条新数据,也就是说,一条 REPLACE 语句有时候等价于一条。INSERT 语句,有时候等价于一条 DELETE 语句加上一条 INSERT 语句。
————————————————
版权声明:本文为CSDN博主「Jack小强」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/yjqyyjw/article/details/68943641

六类触发器

  • BEFORE INSERT - 在将数据插入表格之前激活。
  • AFTER INSERT - 将数据插入表格后激活。
  • BEFORE UPDATE - 在更新表中的数据之前激活。
  • AFTER UPDATE - 更新表中的数据后激活。
  • BEFORE DELETE - 在从表中删除数据之前激活。
  • AFTER DELETE - 从表中删除数据后激活。

for each row

for each row表示行级触发器。

目前MySQL仅支持行级触发器,不支持语句级别(又叫做表级触发器)的触发器(例如create table等语句)。for each row表示更新(insert、update或者delete)操作影响的每一条记录都会执行一次触发程序。

old关键字与new关键字

  • old关键字

当从表中删除某条旧记录时,在触发程序中可以使用old关键字表示旧记录,当需要访问旧记录的某个字段值时,可以使用old.字段名的方式访问。

  • new关键字

当向表插入新记录时,在触发程序中可以使用new关键字表示新记录,当需要访问新记录的某个字段值时,可以使用new.字段名的方式访问。

在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;

注意:OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。
————————————————
版权声明:本文为CSDN博主「Jack小强」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/yjqyyjw/article/details/68943641

查看触发器

这里介绍查看触发器的三种方法

通过show triggers语句

可以使用show triggers语句查看触发器信息

1
show triggers;

上面的命令,执行后看到的东西会相对比较混乱,可以在show trigger后面加上\G,这样可以使显示的信息比较工整有条理。

1
show triggers \G

查询information_schema数据库

MySQL中所有触发器的定义都存放在information_schema数据库下的triggers表中,查询triggers表,可以查看所有数据库中所有触发器的详细信息,查询语句如下:

1
select * from information_schema.triggers \G

使用show create trigger命令

例如查看触发器organization_delete_before_trigger的定义。

1
show create trigger organization_delete_before_trigger \G

删除触发器

可以使用drop trigger语句将该触发器删除

语法格式如下:

1
drop trigger 触发器名

修改触发器

要修改触发器,必须先将其删除,然后使用新代码重新创建。

实例

该部分引用了李福翠老师在超星上发布的课程资料

实践所用数据

  • 建立数据库
1
Create Database Student2 default character set utf8 default COLLATE utf8_bin;
  • 使用当前数据库
1
Use Student2;
  • 建表
1
2
3
4
5
6
Create Table Student(
SNo char(20) primary key,
SName char(20) ,
SSex char(4) default '男',
Sage int
) ENGINE=InnoDB;

在已存在的学生表student中增加一个sdept(系)的新的属性列;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
alter table Student add Dept char(20);


Create Table Course(
CNo char(20) primary key,
CName char(20) NOT NULL,
CRedit float
) ENGINE=InnoDB;

Create Table SC(
SNo char(20) NOT NULL,
CNo char(20) NOT NULL,
Grade float,
Primary Key(SNo, CNo),
Foreign Key(SNo) References Student(SNo) On Delete Cascade,
Foreign Key(CNo) References Course(CNo)
) ENGINE=InnoDB;
  • 插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#学生表student的数据
Insert Into Student Values('991201', '张三', '男', 22, '计算机科学与技术系');
Insert Into Student Values('991202', '李四', '男', 21, '信息科学系');
Insert Into Student Values('991101', '王五', '男', 23, '数理系');
Insert Into Student Values('991102', '陈六', '男', 19, '计算机科学与技术系');
Insert Into Student Values('991103', '吴七', '女', 24, '数理系');
Insert Into Student Values('000101', '刘八', '女', 22, '信息科学系');

#课程表course的数据
Insert Into Course Values('1', '数学', 5);
Insert Into Course Values('2', '数据结构', 4);
Insert Into Course Values('3', '程序设计', 2);
Insert Into Course Values('4', '数据库原理', 3);
Insert Into Course Values('5', '操作系统', 3);

#选课表SC的数据:
Insert Into SC Values('991201', '1', 90);
Insert Into SC Values('991201', '5', 80);
Insert Into SC Values('991201', '3', 85);
Insert Into SC Values('991201', '4', 90);
Insert Into SC Values('991102', '1', 85);
Insert Into SC Values('991102', '2', 98);
Insert Into SC Values('000101', '2', 91);

题目

1.对student表创建delete触发器,当删除某个学生记录时,同时在选课表SC中删除该学生的选课记录。

参考:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from student ; 
select * from sc ;

delimiter //
create trigger t1 AFTER delete on student
FOR EACH ROW
BEGIN
Delete from SC WHERE sno = old.sno;
END
//

delimiter ;
delete from student where SNo='000101';
select * from student ;
select * from sc ;

2.在student表上定义了一个update触发程序,用于检查更新sage时将使用的新值,小于16时取得16,大于40时取40,并更改值。触发器创建成功后,将student表中的SNO值为991103的学生年龄修改为2

参考:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
delimiter //
create trigger upd_check before update on student
FOR EACH ROW
begin
if new.sage<16 then
set new.sage=16;
elseif new.sage>60 then
set new.sage=40;
end if;
end;
//

delimiter ;
select * from student;
update student set sage=5 where sno='991103';
select * from student ;

3.在student表上定义了一个insert触发程序,用于检查插入ssex时只能取男和女,输入其它时取NULL。触发器创建成功后,向student表中插入记录(‘000106’, ‘陈规则’, ‘五’, 22, ‘信息科学系’)

参考:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter //
create trigger ins_check before insert on student
FOR EACH ROW
begin
if new.ssex<>'男' and new.ssex <>'女' then
set new.ssex =null;
end if;
end;
//

delimiter ;
select * from student;
Insert Into Student Values('000106', '陈规则', '五', 22, '信息科学系');
select * from student;

参考文献

在线资源

其他博客

【mysql】触发器的实例详解_Jack小强-CSDN博客

MySQL触发器 - MySQL教程™ (yiibai.com)

MySQL 触发器 | 新手教程 (begtut.com)

MySQL查看触发器 (biancheng.net)

参考书籍

MySQL5.7从零开始学:视频教学版 / 王英英,李小威编著. -北京:清华大学出版社,2018

其他

部分内容来自李福翠老师的上课内容及超星上的资料