触发器
创建表,并添加数据
创建学科表,并添加数据
CREATE TABLE course( id INT(3) PRIMARY KEY auto_increment, name VARCHAR(32), count INT(3) DEFAULT 0 ) INSERT INTO course(name) VALUES('Java'); INSERT INTO course(name) VALUES('Python'); INSERT INTO course(name) VALUES('HTML');
创建班级表,并添加数据
CREATE TABLE class( id INT(3) PRIMARY KEY auto_increment, name VARCHAR(32), count INT(3) DEFAULT 0 ) INSERT INTO class(name) VALUES('2401javaee'); INSERT INTO class(name) VALUES('2402javaee'); INSERT INTO class(name) VALUES('2403javaee'); INSERT INTO class(name) VALUES('2401python'); INSERT INTO class(name) VALUES('2402python'); INSERT INTO class(name) VALUES('2401html'); INSERT INTO class(name) VALUES('2402html');
创建学生表,并添加数据
CREATE TABLE student( id INT(3) PRIMARY KEY auto_increment, name VARCHAR(32), course_id INT(3), class_id INT(3) ) INSERT INTO student(name,course_id,class_id) VALUES('马智威',1,3); INSERT INTO student(name,course_id,class_id) VALUES('江星谊',1,3); INSERT INTO student(name,course_id,class_id) VALUES('aaa',1,1); INSERT INTO student(name,course_id,class_id) VALUES('bbb',2,4); INSERT INTO student(name,course_id,class_id) VALUES('ccc',2,5); INSERT INTO student(name,course_id,class_id) VALUES('ddd',2,5);
需求:添加学生,并更新学科表和班级表的数据
创建后置触发器
监听学生表的插入操作,一旦学生表插入数据之后,就更新学科表和班级表的数据
delimiter xx -- 设置结束符为xx
TRIGGER -- 触发器
AFTER -- 后置触发
new.字段 -- 新增的字段数据
delimiter xx CREATE TRIGGER tri01 AFTER INSERT ON student FOR EACH ROW BEGIN UPDATE course SET count=count+1 WHERE id=new.course_id; UPDATE class SET count=count+1 WHERE id=new.class_id; END xx delimiter ;
需求:删除学生,并更新学科表和班级表的数据
创建前置触发器
监听学生表的删除操作,一旦学生表删除数据之前,先更新学科表和班级表的数据
BEFORE - 前置触发
old.字段 -- 原有的字段数据
delimiter xx CREATE TRIGGER tri02 BEFORE DELETE ON student FOR EACH ROW BEGIN UPDATE course SET count=count-1 WHERE id=old.course_id; UPDATE class SET count=count-1 WHERE id=old.class_id; END xx delimiter ; DELETE FROM student WHERE id=6;
需求:更新学生信息,并更新学科表和班级表的数据
创建前置触发器
监听学生表的更新操作,在学生表更新数据之后,更新学科表和班级表的数据
delimiter xx CREATE TRIGGER tri03 AFTER UPDATE ON student FOR EACH ROW BEGIN UPDATE course SET count=count-1 WHERE id=old.course_id; UPDATE course SET count=count+1 WHERE id=new.course_id; UPDATE class SET count=count-1 WHERE id=old.class_id; UPDATE class SET count=count+1 WHERE id=new.class_id; END xx delimiter ; UPDATE student SET course_id=3,class_id=7 WHERE name='马智威';
删除触发器
DROP TRIGGER tri01; DROP TRIGGER tri02; DROP TRIGGER tri03;