前言

本篇为我个人学习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 ;

触发时间

触发器的触发时间有两种：before与after。

  • 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

其他

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