【MYSQL学习】触发器

数据库的触发器是由事件来触发某个操作，这些事件包括insert语句，update语句和delete语句。不需要用什么进行调用，只要一个预定义的事件发生就会被自动调用。

数据源

create table t_dept(
deptno int(4),
deptname varchar(20),
product varchar(20),
location varchar(20));

insert into t_dept
values
(1,'develop department','pivot_gaea','west_3'),
(2,'test department','sky_start','east_4'),
(3,'operate department','cloud_4','south_4'),
(4,'maintain department','fly_4','north_5');

create table t_logger(
lid int(11),
tablename varchar(20),
ltime datetime);

desc t_logger;

创建触发器

语法形式：

create trigger （给这个触发器取个名字）
	before|after （事件：insert，delete，update） on （表名） for each row
		（触发后被执行的语句）;

• 创建有一条执行语句的触发器

在往t_dept表插入数据之前先在日志表中插入

create trigger tri_loggertime
	before insert on t_dept for each row
		insert into t_logger values (null,'t_dept',now());

测试下：

insert into t_dept values (1,'HR','pivot_geea','west_3');
select * from from t_dept;
select * from from t_logger;

• 创建有多条执行语句的触发器
  语法：

create trigger （给这个触发器取个名字）
before|after （事件：insert，delete，update） on （表名） for each row
	begin
	（触发后被执行的语句）
	end;

和创建一条执行语句相比，这里多了关键字begin和end，在这两个关键字之间放置执行语句，执行语句之间用分号隔开。

delimiter $$
create trigger tri_loggertime2
	after insert
		on t_dept for each row
	  BEGIN
			insert into t_logger values (null,'t_dept',now());
			insert into t_logger values (null,'t_dept',now());
		end;
		$$
delimiter ;

insert into t_dept values (2,'test department','sky','east_4');

select * from t_logger;

查看触发器

use infomation_schema;

select * from t_logger where trigger_name = 'tri_loggertime2';

删除触发器

drop trigger tri_loggertime;

综合示例——创建并使用触发器

数据准备

create table t_class(
classno int(11) default null,
cname varchar(20) default null,
loc varchar(40) default null,
advisor varchar(20) default null,
studentCount int(4) default 0);

insert into t_class(classno,cname,loc,advisor,studentCount)
values
(1,'class_1','loc_1','advisor_1',0),
(2,'class_2','loc_2','advisor_2',0),
(3,'class_3','loc_3','advisor_3',0),
(4,'class_4','loc_4','advisor_4',0);


create table t_student (
id int(10) DEFAULT NULL,
tname varchar(20) DEFAULT NULL,
gender varchar(10) DEFAULT NULL,
age int(4) DEFAULT NULL,
classno int(11) DEFAULT NULL,
totalScore int(4) default 0);

insert into t_student(id,tname,gender,age,classno,totalScore)
values (1001,'Alicia Florric','Female',33,1,0),
(1002,'Kalinda Sharma','Female',31,1,0),
(1003,'Cary Agos','Male',27,1,0),
(1004,'Diane Lockhart','Female',43,2,0),
(1005,'Eli Gold','Male',44,3,0),
(1006,'Peter Florric','Male',34,3,0),
(1007,'Will Gardner','Male',38,2,0),
(1008,'Jackquiline Florriok','Male',38,4,0),
(1009,'Zach Florriok','Male',14,4,0),
(1010,'Grace Florriok','Male',12,4,0);


create table t_score(
stuid int(11) not null,
Chinese int(4) default 0,
English int(4) default 0,
Math int(4) default 0,
Chemistry int(4) default 0,
Physics int(4) default 0);

insert into t_score (stuid,Chinese,English,Math,Chemistry,Physics)
values
(1001,90,89,92,83,80),
(1002,92,98,92,93,90),
(1003,79,78,82,83,89),
(1004,89,92,91,92,89),
(1005,92,95,91,92,89),
(1006,90,91,92,94,92),
(1007,91,90,83,88,93),
(1008,90,81,84,86,98),
(1009,91,84,85,86,93),
(1010,88,81,82,84,99);

• 增减班级人数

现在数据库中有三张表，分别是班级表，学生表和学生成绩表，每次向学生成绩表中新插入一个学生，班级表中对应的班级人数就会增加1，如果删除一个学生，对应的人数就会减1。

delimiter $$
create trigger update_stu_count
AFTER insert on t_student for each row
BEGIN
	update t_class set studentCount = studentCount+1
	where classno = new.classno;
end;
$$
delimiter ;

NEW 与 OLD 详解

上述示例中使用了NEW关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。
具体地：
INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2019”）。
测试下结果：

insert into t_student values
(1,'xxj','F',18,1,0),
(3,'xxj2','F',18,1,0),
(4,'xxj3','F',18,2,0);

select * from t_student;
select * from t_class;

• 学生成绩表中更新一条记录，就会改变总分值的计算

insert into t_score values (1,79,80,90,90,80);
select * from t_score;
select * from t_student;

update t_score set Chinese=90;
select * from t_score;
select * from t_student;