MySQL进阶——触发器
作者:mmseoamin日期:2023-12-20

1.触发器定义

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

2. 创建触发器语法

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开始,可以为一张表定义具有相同触发事件和触发时机的多个触发器。默认情况下,具有相同触发事件和触发时机的触发器按其创建顺序激活。

3. 触发器

3.1 after触发器

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

3.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

3.1.2 new和old

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

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

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

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

3.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

3.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();

未调用存储过程前:

MySQL进阶——触发器,第1张

调用存储过程后:

MySQL进阶——触发器,第2张 

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);

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"

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','刘洋');

插入学生信息之前:

MySQL进阶——触发器,第3张

插入学生信息之后:

MySQL进阶——触发器,第4张 

6. 查看触发器

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

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

7. 删除触发器

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

DROP TRIGGER trigger_name

MySQL进阶——触发器,第5张