文章目录

触发器

1. 课前题目

给student表建立一个credit 学分列，记载每个同学已经获得的学分（学习的课程大于60分才算获得学分,每门课程学分在course表中有)
写一个存储过程p_c,输入班级号，可以重新计算该班级中的同学已经获得的学分并更新在对应的credit上。

#新增一个列
alter table student add credit int;

#先找出你输入班级对应的学生,根据这些学生找出他们大于成绩60分的课程号。
#将学号、课程号和成绩用游标遍历，

CREATE PROCEDURE p_c(IN cid VARCHAR(20))
BEGIN
	DECLARE coid,sid VARCHAR(20);
	DECLARE gra int;
	DECLARE done boolean DEFAULT true;
	DECLARE cur CURSOR FOR
		SELECT studentid,courseid,grade
		FROM grade
		WHERE studentid IN(
			SELECT studentid
			FROM student
			WHERE classid = cid
		) AND grade>=60;
	DECLARE CONTINUE HANDLER FOR NOT found SET done = false;
	#学分初始值要清零而不是让它为null,因为null和数值相加依然为null。
	#修改学分一定要满足课程和学号的值确定。
	UPDATE student SET credit = 0 
	WHERE studentid 
	IN(SELECT studentid FROM 
	#为什么要加一个中间临时表？因为在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，
		(SELECT * FROM student)a 
		WHERE ClassID = cid);

	OPEN cur;
	FETCH cur INTO sid,coid,gra;
	while done Do
		UPDATE student SET credit = credit+(
				SELECT credit
				FROM	course 
				WHERE courseid = coid)	WHERE StudentID = sid;
		FETCH cur INTO sid,coid,gra;
	END while;
	CLOSE cur;
END;

CALL p_c('Cs010902');

结果(可以看到只要是这个班级的学生学分都加上了)：

image-20201124195444727

2. 触发器定义

同存储过程和函数类似，MySQL中的触发器也是存储在系统内部的一段程序代码，可以把它看作是一个特殊的存储过程。所不同的是，触发器无需人工调用，当程序满足定义条件时就会被MySQL自动调用。这些条件可以称为触发事件，包括INSERT、UPDATE和DELETE操作。

3. 创建触发器语法

CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name FOR EACH ROW 

trigger_body

trigger_time：触发器触发时机，有before和after

trigger_event：触发器触发事件，有insert,update,delete三种

trigger_body：触发器主体语句

从MySQL5.7开始，可以为一张表定义具有相同触发事件和触发时机的多个触发器。默认情况下，具有相同触发事件和触发时机的触发器按其创建顺序激活。

4. 触发器

4.1 after触发器

AFTER触发器是指触发器监视的触发事件执行之后，再激活触发器，激活后所执行的操作无法影响触发器所监视的事件。

4.1.1 delete触发器

建一个触发器t_d_s，当删除表student中某个学生的信息时，同时将grade表中与该学生有关的数据全部删除。

CREATE TRIGGER trigger_t1
AFTER DELETE ON student
FOR EACH ROW
BEGIN 
	DELETE FROM grade WHERE studentid = old.studentid;
END
4.1.2 new和old

触发器不会产生new表和old表，所谓new，old只是指insert,delete,update操作执行前的所在表状态和执行后的状态

对insert而言，只有new合法，新插入的行用new来表示，行中每一列的值用new.列名来表示

对于delete而言，只有old合法，删除的行用old来表示，行中每一列的值用old.列名来表示

对于update而言，被修改的行，修改前的数据，用old来表示，old.列名；修改后的数据，用new来表示，new.列名

4.1.3 UPDATE

创建一触发器t_u_s，实现在更新学生表的学号时，同时更新grade表中的相关记录的studentid值。

CREATE TRIGGER t_u_s
AFTER UPDATE ON student
for EACH ROW
BEGIN
	UPDATE grade SET studentid = new.studentid WHERE studentid = old.studentid;
END
4.1.4 INSERT
  1. 创建一个存储过程，根据student表中数据，一次性更新class表中每个班的人数
CREATE PROCEDURE p_tao()
BEGIN
	DECLARE num int;
	DECLARE cid VARCHAR(20);
	DECLARE done boolean DEFAULT true;
	DECLARE cur CURSOR FOR
		SELECT classid,COUNT(*)
		FROM student
		GROUP BY classid;	
	DECLARE CONTINUE HANDLER FOR NOT found SET done = false;
	
	UPDATE class set studentnum = 0;
	OPEN cur;
	FETCH cur INTO cid,num;
	WHILE	done DO		
		UPDATE class SET StudentNum = num WHERE classid = cid;	
		FETCH cur INTO cid,num;	
	END WHILE;
	CLOSE cur;
END

CALL p_tao();

未调用存储过程前：

image-20201123183427951

调用存储过程后：

image-20201123183524881

  1. 创建一个触发器t_i_s，当student表插入新学生时，class表中该班级人数加1
CREATE TRIGGER t_i_s
AFTER INSERT
ON student
for EACH ROW
BEGIN
	UPDATE class SET studentnum = studentnum + 1 WHERE classid = new.classid;
END;

image-20201123184017192

4.2 before触发器

BEFORE触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断，或修改即将发生的操作。

Before与After区别：

before：(insert、update)可以对new进行修改，after不能对new进行修改，三者都不能修改old数据。

4.2.1 INSERT

给teacher表创建一个列， salary列，记录教师的工资

建一个触发器t_d_t，插入教师信息时，如果教师工资小于3000，则自动调整成3000

#给teacher表创建一个列， salary列，记录教师的工资
ALTER table teacher ADD salary int;
#建一个触发器tdt，插入教师信息时，如果教师工资小于3000，则自动调整成3000
CREATE TRIGGER tdt 
BEFORE INSERT 
ON teacher
for each ROW
BEGIN
	if new.salary <3000 THEN SET new.salary = 3000;
  END if;
END;

INSERT INTO teacher(TeacherID,Teachername,sex,salary) VALUES('123','位老师','女',2999);
INSERT INTO teacher(TeacherID,Teachername,sex,salary) VALUES('124','文老师','男',3001);

image-20201123184742167

4.2.2 UPDATE

给grade表建立一个学分列，并创建一个触发器，当修改grade表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分

ALTER TABLE grade ADD credit int;

CREATE TRIGGER trigger_ch 
BEFORE UPDATE
ON grade
FOR EACH ROW
BEGIN
	IF new.grade<60	THEN SET new.credit = 0;
	ELSE SET new.credit = (
		SELECT credit
		FROM coure
		WHERE courseid = new.courseid
	);
	END if;
END

UPDATE grade SET grade = 50 WHERE courseid ="Dp010001" AND studentid = "St0109010003"

修改grade之前：

image-20201123185748944

修改grade之后：

image-20201123190654448

5. 中断触发器

假设软件B1802班级最多只能有4个人，当往b_student表中增加新生信息时，b_class班级表内学生人数会随之增加，当人数大于4人时，由于超过人数限制，会报系统错误，错误提示为“超过人数限制”，并且该触发器所有操作（包括引发触发器的操作）均不能成功。

#创建b_classs表存放班级人数，如果班级人数大于4就提示"超出人数限制"
CREATE TABLE B_class(
	Cid VARCHAR(20) PRIMARY KEY  COMMENT "班级名称",
	num int COMMENT "人数"
);
#插入初始数值，初始数值软件B1802班没有人
INSERT INTO b_class VALUES("软件B1802",0);


#创建一个b_student表存放学生信息
CREATE TABLE b_student(
	studentid VARCHAR(20) PRIMARY KEY COMMENT "学号",
	studentname VARCHAR(20) not null COMMENT "姓名",
	classid VARCHAR(20)  DEFAULT '软件B1802' COMMENT "班级",
	CONSTRAINT FK_ID FOREIGN KEY(classid) REFERENCES B_class(Cid)
	ON DELETE RESTRICT on UPDATE CASCADE
)DEFAULT CHARSET = utf8;


#要注意的是，我们在插入学生信息的时候，我们要使用触发器来更新班级表中的人数，不然尽管你在学生信息表插入多条数据，班级表中的人数会一直保持不表，也就不会出现超出人数限制的情况。
CREATE TRIGGER add_trigger
AFTER INSERT
ON b_student
for EACH ROW
BEGIN
	UPDATE b_class SET num = num + 1 WHERE Cid = new.classid;
END;


#创建抛出自定义异常的触发器，当插入学生人数超过4人时，就会抛出异常。
CREATE TRIGGER exception BEFORE INSERT ON b_student for each row
BEGIN
	DECLARE number int;
	SELECT num INTO number from b_class WHERE cid = new.classid;
	if number = 4  THEN	SIGNAL SQLSTATE '45000'	
	SET message_text = '超出人数限制',MYSQL_ERRNO = 1333;	
	END if;
END;

INSERT INTO b_student(studentid,studentname) VALUES('238','位傲气'),('239','阮氏问'),('240','王陇镇'),('250','周志豪');


INSERT INTO b_student(studentid,studentname) VALUES('241','刘洋');

插入学生信息之前：

image-20201123200910525

image-20201123201654552

插入学生信息之后：

image-20201123200954698

image-20201123201213825

抛出异常：

image-20201123201543105

如果需要中断触发器的执行，可以使用signal语句抛出一个异常，并向处理程序，应用程序的外部部分或客户端提供错误信息。

语法：

SIGNAL SQLSTATE | condition_name;

SET condition_information_item_name_1 = value_1,

condition_information_item_name_2= value_2;

SIGNAL SQLSTATE | condition_name关键字是由DECLARE CONDITION语句声明的SQLSTATE值或条件名称。

要向调用者提供信息，请使用SET子句。如果要使用值返回多个条件信息项名称，则需要用逗号分隔每个名称/值对。

condition_information_item_name可以是MESSAGE_TEXT，MYSQL_ERRORNO，CURSOR_NAME等。

6. 查看触发器

在MySQL5.7以前，对同一个表相同触发时机的相同触发事件，只能定义一个触发器。例如，对于某个表的不同字段的AFTER更新触发器，只能定义成一个触发器，在触发器中通过判断更新的字段进行相应的处理。所以在创建触发器之前，最好能够查看MySQL中是否已经存在该触发器。

MySQL中，查看触发器有两种方法，一种是使用SHOW TRIGGERS语句，一种是SHOW CREATE TRIGGERS TRIGGERNAME 查看触发器的详细信息。

使用SHOW TRIGGERS语句可以查看MySQL中已经存在的触发器，基本语法形式如下：

6.1 查看全部触发器

SHOW TRIGGERS

image-20201123201910058

使用SHOW CREATE TRIGGERS语句可以查看触发器定义，基本语法形式如下：

6.2 查看触发器的创建语句

SHOW CREATE TRIGGER TRIGGERNAME

image-20201123202839181

7. 删除触发器

使用DROP TRIGGER语句可以删除MySQL中定义的触发器，基本语法形式如下：

DROP TRIGGER trigger_name;

image-20201124193244569

8. 参考

一篇很棒的 MySQL 触发器学习教程

mysql之触发器trigger

MySQL | 触发器

9. 实验：

  1. 定义一个触发器，为student表定义完整性规则“插入学生的信息时，性别只能为男或者女，如果输入除了男女之外的性别，则自动改为“男”

    CREATE TRIGGER trigger_work1
BEFORE INSERT
ON student
FOR EACH ROW
BEGIN
	IF new.sex NOT IN('男','女') THEN SET new.sex = '男';
	END IF;
END;

INSERT INTO student(studentid,studentname,sex,classid) VALUES ("299",'匿名','变态','Cs011104');
INSERT INTO student(studentid,studentname,sex,classid) VALUES ("292",'匿','女','Cs011104');

    image-20201123205112042

  2. （1）修改grade表，新增一个“remark”列，数据类型为char（10），给出修改的SQL语句。

    ALTER TABLE grade add remark char(10);

    image-20201123205323257

    （2）在学生表中增加一列sum_credit，将每个同学的获得的学分插入到同学对应的sum_credit列中。

    ALTER TABLE student ADD sum_credit int;

CREATE PROCEDURE sum_credit()
BEGIN
	#sid,coid,gra分别存放学号、课程号和分数
	DECLARE sid,coid VARCHAR(20);
	DECLARE gra int;
	#定义循环初始条件为true
	DECLARE done boolean default true;
	#定义游标，游标查询的结果就是grade表中的学号、课程号和分数
	#因为根据题目要求是分数大于等于60分才有相应课程的学分，所以我在此处已经将大于60分的信息筛选出来
	DECLARE cur CURSOR FOR
		SELECT studentid,courseid,grade
		FROM grade
		WHERE grade>60;
	#当游标查询不到记录时将done的值改为false，结束循环
	DECLARE CONTINUE HANDLER FOR NOT found SET done = false;
	#初始的学分不能为空值，空值和任何值相加等于空值，必须初始化为0
	#SELECT null+2,可以看到null+2=null;
	UPDATE 	student SET sum_credit = 0;
	
	OPEN cur;
	FETCH cur INTO sid,coid,gra;
	while done DO
	#将student表中的sum_credit循环加起来
		UPDATE student SET sum_credit = sum_credit + (
			SELECT credit
			FROM course
			WHERE courseid = coid
		)WHERE StudentID = sid;
		FETCH cur INTO sid,coid,gra;
	END while;
	CLOSE cur;
END;

    调用存储过程前：

    image-20201123224443034

    调用存储过程后：

    image-20201123224606366

    image-20201123224628029

    （3）在成绩表中创建一个触发器，当对该表录入成绩信息时，如果分数小于60分，则将备注列的内容写为“NO”，如果大于60，则将该门课学分加在该名同学的sum_credit上，如果成绩大于100或者小于0分，中断触发器，提示“分数不合理”

    CREATE TRIGGER trigger_u_g
BEFORE INSERT
ON grade
FOR EACH ROW
BEGIN
	if new.grade<0 OR new.grade>100 THEN	SIGNAL SQLSTATE '45000'	
	SET message_text = '分数不合理',MYSQL_ERRNO = 1333;
	ELSEIF new.grade<60 THEN SET new.remark = 'NO';
	ELSE UPDATE student SET sum_credit = sum_credit+(
		SELECT credit
		FROM course
		WHERE courseid = new.CourseID
	)	WHERE studentid = new.studentid;
	END if;
END

INSERT INTO grade(CourseID,studentid,Semester,grade) VALUES("Dp020001","St0210010006",2,101);
INSERT INTO grade(CourseID,studentid,Semester,grade) VALUES("Dp020002","St0210010006",2,50);
INSERT INTO grade(CourseID,studentid,Semester,grade) VALUES("Dp030001","St0210010006",2,80);
SELECT * FROM grade
WHERE StudentID = "St0210010006"

    image-20201124135848770

    可以看到雷立同学的学分开始是为0的，在grade表中没有他的信息，估计是缺考了。或者老师把他成绩没录进去。现在涛老师来录入他的成绩。他就大学英语80分，所以根据下面的课程表可以看到他及格了就有6学分。那么让我们看最后他的sum_credit是不是6.

    image-20201124135910037

    当插入第一条分数为101分的信息时，出现报错：

    image-20201124135935965

    当插入小于60分的信息时，可以看到备注列有“NO”：

    image-20201124140246990

    查看该学生在grade表中的信息，可以看到只录入了两条学生信息，当输入分数不合理时，中断了触发器：

    image-20201124140424792

    果然，雷立同学拿到了6学分

    image-20201124141036437

    （4) 在成绩表中创建一个触发器，当对该表修改成绩信息时，如果分数本身大于60而修改后小于60分，则将备注列的内容写为“NO”，并在对应同学的sum_credit数据中减去这门课的学分，如果本身小于60而修改后大于等于60，则将该门课学分加在该名同学的sum_credit上，其他情况备注和总学分都不变,如果修改的成绩大于100或者小于0分，中断触发器，提示“分数不合理”

    CREATE TRIGGER trigger_u_g_g
BEFORE UPDATE
ON grade
FOR EACH ROW
BEGIN
	if new.grade<0 OR new.grade>100 THEN	SIGNAL SQLSTATE '45000'	
	SET message_text = '分数不合理',MYSQL_ERRNO = 1333;
	ELSEIF old.grade>60 AND new.grade<60 
	THEN 
		SET new.remark = 'NO';
		UPDATE student SET sum_credit = sum_credit-(
		SELECT credit
		FROM course
		WHERE courseid = new.CourseID
	)	WHERE studentid = new.studentid;
	ELSEIF old.grade<60 AND new.grade>=60 
	THEN
		UPDATE student SET sum_credit = sum_credit+(
		SELECT credit
		FROM course
		WHERE courseid = new.CourseID
	)	WHERE studentid = new.studentid;
	END if;
END


UPDATE grade SET grade = 50 WHERE StudentID = 'St0210010006' AND CourseID = 'Dp030001';
UPDATE grade SET grade = 90 WHERE StudentID = 'St0210010006' AND CourseID = 'Dp020002';

    在上一题我们看到雷立 同学6学分，当时课程号是Dp03001及格了，6学分，现在我修改他的这个成绩，改为50分，他的学分就应该为0，且备注为“NO”。image-20201124143501731

    image-20201124143538730

    然后我再把他之前那门4学分但是他没及格的课程分数改为90分，这样他就有4学分了

    注意啊，我这个是用内连接查询（取两个表的交集）的，不是每门课程得了4学分，是他的总学分为4学分。而且题目没要求改为90分后，那个备注列的"NO"要改为空或者“YES”

    SELECT StudentName,CourseID,s.StudentID,grade,remark,sum_credit 
FROM grade g INNER JOIN Student s
ON s.studentid = g.studentid AND 
s.StudentID = "St0210010006"

    image-20201124144841794

    当我改成负数分数时会抛出自定义异常：

    image-20201124183711785

  3. 综合存储过程和触发器完成这个需求：

    一个老师修改成绩时，后台调用存储过程和触发器，综合完成：

    （1）在修改成绩之前，首先需要查看该同学该课程的成绩是否已经在表里存在了；

    （2）如果该同学课程成绩未被录入，提示成绩未录入；

    （3）如果该同学课程成绩已录入，修改分数差值不超过20分，正常修改。

    （4）如果该同学课程成绩已录入，修改分数差值超过20分，则修改完成后会将这条成绩信息录入成绩复核表（包括学生号，课程号，修改前成绩，修改后成绩）。

成绩复核表自己创建。此处我的成绩复核表为grade_copy

#创建成绩复核表
CREATE TABLE grade_copy(
	StudentID VARCHAR(20) PRIMARY KEY COMMENT '学号',
	CourseID VARCHAR(20) COMMENT '课程号',
	old_grade int COMMENT '原来的成绩',
	new_grade int COMMENT '修改后的成绩'
);

#创建该存储过程check_g通过输入进来的学号和课程号来输出他的分数
CREATE PROCEDURE check_g(IN sid VARCHAR(20),IN coid VARCHAR(20),OUT fs INT)
BEGIN
	SELECT grade INTO fs
	FROM grade
	WHERE StudentID = sid
	AND courseid = coid;
END;

#该触发器是对分数修改之后的操作，为了验证grade表中是否有学生成绩，没有就抛异常，有就直接修改成绩
CREATE TRIGGER frist
BEFORE UPDATE
ON grade
FOR EACH ROW
BEGIN
	DECLARE f_s INT;#定义临时变量来存放找到的学生成绩
	CALL check_g(new.StudentID,new.CourseID,@oldfs);
	IF (@oldfs is null) THEN SIGNAL SQLSTATE '45000'	
	SET message_text = '成绩未录入',MYSQL_ERRNO = 1334;
	ELSE 
	#以下两句看上去很奇怪，但是我如果不用临时变量f_s去存放new.grade，而是grade = new.grade就会报错，
	#因为此时将grade当作一个变量来使用了，但是并没有定义过这个变量。所以用临时变量f_s存放后
	SET f_s = new.grade;
	#所以用临时变量f_s存放后，又重新赋给了new.grade
	SET new.grade = f_s;
	end if;
END;



#该触发器是对分数修改之后的操作，分数差值大于20分就调用存储过程，将大于20分的学生学号和课程号传给存储过程copy。反之，不做处理。
CREATE TRIGGER second_t
AFTER UPDATE
ON grade
for EACH ROW
BEGIN
		#注意分数差值要用绝对值ABS计算，因为可能改后分数和改分数的差有可能为负数
	if ABS(new.grade-old.grade)>20
		THEN CALL copy(new.StudentID,new.CourseID);
	END if;
END;


#该存储过程用来复制表内数据，将触发器中得到的学号和课程号来找出grade表中现在的新成绩，然后因为初始成绩是全局变量，可以直接使用，然后一起写进成绩复核表
CREATE PROCEDURE copy(IN sid VARCHAR(20),IN coid VARCHAR(20))
BEGIN
	#定义临时变量来存放修改后的新成绩
	DECLARE new_grade int;
	#这里开始我没有定义临时变量，直接selecet grade,然后把grade写入插入语句，但是会报错，因为触发器中不能返回一个结果集，如果不将它赋给一个临时变量，它查询的就是一个结果。
	SELECT grade into new_grade
	FROM grade
	WHERE StudentID = sid AND CourseID = coid;
	INSERT INTO grade_copy VALUES(sid,coid,@oldfs,new_grade);
END;

①. 先加入一条记录显示雷立同学的成绩为空，也就是未录入。

image-20201124181421213

测试：

image-20201124181512979

②. 现在雷立同学的Dp03001课程成绩为50分，我要改成65分，分数差值小于20分。

image-20201124181833300

同时成绩复核表也没有出现增加：

image-20201124182237512

③. 现在我再把该同学的Dp02002课程成绩从90分改为60分，分数差值为30分大于20分。

image-20201124182553132

成绩复核表中也新增了记录：

image-20201124183254086

# mysql
Logo
华为开发者空间

华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。

更多推荐

cover

COC云运维中心新特性解读，让智能运维更高效

avatar 华为开发者空间
cover

GaussDB性能调优

avatar 华为开发者空间
cover

简单几步，基于云主机快速为Web项目添加AI助手

avatar 华为开发者空间