MySQL进阶——触发器

2023-10-28 10:24

短信预约 -IT技能 免费直播动态提醒

1.触发器定义

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

2. 创建触发器语法

CREATE TRIGGER trigger_name trigger_time trigger_eventON table_name FOR EACH ROW trigger_bodytrigger_time：触发器触发时机，有before和aftertrigger_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_t1AFTER DELETE ON studentFOR EACH ROWBEGIN 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_sAFTER UPDATE ON studentfor EACH ROWBEGINUPDATE grade SET studentid = new.studentid WHERE studentid = old.studentid;END

3.1.4 INSERT

创建一个存储过程，根据student表中数据，一次性更新class表中每个班的人数

CREATE PROCEDURE p_tao()BEGINDECLARE num int;DECLARE cid VARCHAR(20);DECLARE done boolean DEFAULT true;DECLARE cur CURSOR FORSELECT classid,COUNT(*)FROM studentGROUP BY classid;DECLARE CONTINUE HANDLER FOR NOT found SET done = false;UPDATE class set studentnum = 0;OPEN cur;FETCH cur INTO cid,num;WHILEdone DOUPDATE class SET StudentNum = num WHERE classid = cid;FETCH cur INTO cid,num;END WHILE;CLOSE cur;ENDCALL p_tao();

未调用存储过程前：

调用存储过程后：

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，则自动调整成3000CREATE TRIGGER tdt BEFORE INSERT ON teacherfor each ROWBEGINif 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 UPDATEON gradeFOR EACH ROWBEGINIF new.grade<60THEN SET new.credit = 0;ELSE SET new.credit = (SELECT creditFROM coureWHERE courseid = new.courseid);END if;ENDUPDATE 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_triggerAFTER INSERTON b_studentfor EACH ROWBEGINUPDATE b_class SET num = num + 1 WHERE Cid = new.classid;END;#创建抛出自定义异常的触发器，当插入学生人数超过4人时，就会抛出异常。CREATE TRIGGER exception BEFORE INSERT ON b_student for each rowBEGINDECLARE number int;SELECT num INTO number from b_class WHERE cid = new.classid;if number = 4  THENSIGNAL 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','刘洋');

插入学生信息之前：