MySQL进阶——触发器

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

未调用存储过程前：

调用存储过程后：

 

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

插入学生信息之前：

插入学生信息之后：

 

6. 查看触发器

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

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

7. 删除触发器

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

DROP TRIGGER trigger_name