触发器 赋值 本表 mysql mysql before触发器

文章目录

• 触发器

• 一、触发器的概述
• 二、触发器的基本操作

• 2.1 after和before

• 2.1.1 after

• delete型
• update型
• insert型

• 2.1.2 before

• insert型
• update型

• 三、练习
• 四、实验题

• 实验一
• 实验二
• 实验三

触发器

一、触发器的概述

触发器可以看作是一种特殊类型的存储过程，它与存储过程的区别在于存储过程使用时需要调用，而触发器是预先定义好的事件（如insert、delete、upodate等操作）发生时，才会被MySQL自动调用。

创建触发器时需要与数据表相关联，当表发生特定事件(如INSERT、DELETE、UPDATE等操作)时，就会自动执行触发器中提前预订好的SQL代码.实现插人数据前,强制检验或转换数据等操作，或是在触发器中代码执行错误后撤销已执行成功的操作，保证数据的安全。因此，不难看出触发器在使用时的优点和缺点，具体内容如下。

(1)优点。

①触发器可以通过数据库中的相关表实现级联无痕更改操作。

②保证数据安全,进行安全校验。

(2)缺点。

①触发器的使用会影响数据库的结构，同时增加了维护的复杂程度。

②触发器的无痕操作会造成数据在程序(如PHP、Java等)层面不可控。

二、触发器的基本操作

触发器的基本操作包括创建触发器)查看触发器、触发器的触发和删除触发器。接下来对触发器的基本操作进行详细讲解。

1.创建触发器
在创建触发器时需要指定触发器的操作对象——数据表，且该数据表不能是临时表或视图。基本语法格式如下：

create trigger 触发器名字 触发时机 触发事件 on 表 for each row 触发顺序
begin
	操作的内容
end;

在上述的语法中，指定数据库下的触发器名称必须唯一，也就是不同数据库下可以含有名称相同的触发器。“ON 表 for each row ”指定触发器的操作对象。
从MySQL5.7开始，可以为一张表定义具有相同触发事件和触发时机的多个触发器。默认情况下，具有相同触发事件和触发时机的触发器按其创建顺序激活。
触发时机表示数据表在发生变化前后的两种状态，触发事件表示激活触发器的操作类型，触发顺序表示指定在同一个表中多个触发器的执行顺序，默认情况下按创建顺序激活。
创建触发器的选项

选项	可选值	描述
触发时机	before	在表中数据发生改变前的状态
触发时机	after	在表中数据发生改变后的状态
触发事件	insert	插入操作
触发事件	update	更新操作
触发事件	delete	删除操作
触发顺序	follows 其他触发器名称	新触发器在现有触发器之后激活
触发顺序	precedes 其他触发器名称	新触发器在现有触发器之前激活

2.1 after和before

2.1.1 after

AFTER触发器可以根据所监视的事件分为三种，分别是INSERT型、UPDATE型和DELETE型，下面分别介绍。

delete型

建一个触发器t_d_s，当删除表student中某个学生的信息时，同时将grade表中与该学生有关的数据全部删除。

create trigger t_d_s after delete on student for each row
delete from grade where studentid = old.studentid;

select * from student;
select * from grade where studentid  = 'St0109010002';
select * from student where studentid  = 'St0109010002';

delete from student where studentid =  'St0109010002';

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

• 对insert而言，只有new合法，新插入的行用new来表示，行中每一列的值用new.列名来表示
• 对于delete而言，只有old合法，删除的行用old来表示，行中每一列的值用old.列名来表示
• 对于update而言，被修改的行，修改前的数据，用old来表示，old.列名；修改后的数据，用new来表示，new.列名

update型

创建一触发器t_u_s，实现在更新学生表的学号时，同时更新grade表中的相关记录的studentid值。

create trigger t_u_s after update on student for each row
update grade set studentid=new.studentid where studentid = old.studentid;

select * from student;
select * from grade where studentid  = 'St0109010003';
select * from student where studentid  = 'St0109010003';


update student set studentid = 'St0109010111' where studentid =  'St0109010003';

insert型

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

create procedure p_c1() #901 #输入班级号
	begin
	declare claid char(20);
	#declare fs int;
	declare found boolean default true;
	#定义游标
	declare sum_c CURSOR for select classid from student;
	declare continue handler for not found set found=false;
	update class set studentnum=0;
	open sum_c;
	fetch sum_c into claid;
	while found do
	update class set studentnum=studentnum+1 where classid=claid;
	fetch sum_c into claid;
	end while;
	close sum_c;
	end;
	
select * from student;
drop procedure p_c1;

call p_c1();

2. 创建一个触发器t_i_s，当student表插入新学生时，class表中该班级人数加1

create trigger t_i_s after insert on student for each row
update class set studentnum = studentnum + 1 where classid = new.classid;
select * from class;
insert into student(studentid,studentname,sex,classid) values("S001","zs","女","Cs010901")

2.1.2 before

BEFORE触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断，或修改即将发生的操作。

BEFORE触发器也可以根据监视事件分为三种，分别是INSERT型、UPDATE型和DELETE型。

Before与After区别：

before：(insert、update)可以对new进行修改，after不能对new进行修改，三者都不能修改old数据。

insert型

给teacher表创建一个列， salary列，记录教师的工资
建一个触发器t_d_t，插入教师信息时，如果教师工资小于3000，则自动调整成3000

alter table teacher add salary int;
create trigger t_d_t before insert on teacher for each row
begin
	if new.salary < 3000 then set new.salary = 3000;
	end if;
end;

select * from teacher;
desc teacher;

insert into teacher(teacherid,teachername,sex,salary) values("dep01011","李峰","男",2000);

update型

给grade表建立一个学分列，并创建一个触发器，当修改grade表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分。

alter table grade add credit int;
create trigger t_g_t before update on grade for each row
begin
	declare xf int;
	select credit into xf from course where courseid = new.courseid;
	if new.grade < 60 then set new.credit = 0;
	else set new.credit = xf;
	end if;
end;

select * from grade;

update grade set grade = 90 where studentid =  'St0109010004';

三、练习

一个班级最多只能有10个人，当往student表中增加新生信息时，班级表内学生人数会随之增加，当人数大于10人时，由于超过人数限制，会报系统错误，错误提示为“超过人数限制”，并且该触发器所有操作（包括引发触发器的操作）均不能成功。

create trigger t_insert_c1 after insert on student for each row
begin 
	# 前面已经有t_i_s的触发器，所以此处不用再写相同语句
	# update class set studentnum = studentnum + 1 where classid = new.classid;
	# 取数据，如果学生信息人数大于10，则报错提示，不能再插入数据
	declare num int;
	select studentnum into num from class where classid = new.classid;
	if num = 39 then
		signal SQLSTATE '45000' set MESSAGE_TEXT = "人数超出",MYSQL_ERRNO = 1333;
	end if;
end;

drop trigger t_insert_c1;
select * from class;
select * from student;
insert into student(studentid,studentname,sex,classid) values("S005","zs","女","Cs010901");

四、实验题

实验一

1. 定义一个触发器，为student表定义完整性规则“插入学生的信息时，性别只能为男或者女，如果输入除了男女之外的性别，则自动改为男”

create trigger t_student 
before insert 
on student 
for each row
begin
	if new.sex not in('男','女') then set new.sex = "男";
	end if;
end;

select * from student;
drop trigger t_student;
desc student;

insert into student(studentid,studentname,sex,classid) values("S005","zs","鱼","Cs010901");
insert into student(studentid,studentname,sex,classid) values("S004","韦小宝","女","Cs010901");

实验二

（1）修改grade表，新增一个“remark”列，数据类型为char（10），给出修改的SQL语句。
（2）在学生表中增加一列sum_credit，将每个同学的获得的学分插入到同学对应的sum_credit列中。
（3）在成绩表中创建一个触发器，当对该表录入成绩信息时，如果分数小于60分，则将备注列的内容写为“NO”，如果大于60，则将该门课学分加在该名同学的sum_credit上，如果成绩大于100或者小于0分，中断触发器，提示“分数不合理”
（4)在成绩表中创建一个触发器，当对该表修改成绩信息时，如果分数本身大于60而修改后小于60分，则将备注列的内容写为“NO”，并在对应同学的sum_credit数据中减去这门课的学分，如果本身小于60而修改后大于等于60，则将该门课学分加在该名同学的sum_credit上，其他情况备注和总学分都不变,如果修改的成绩大于100或者小于0分，中断触发器，提示“分数不合理”

题目一

alter table grade add remark char(10);
alter table student add sum_credit int;

create procedure sum_credit()
begin
	# 声明变量
	declare sid,cid VARCHAR(20);
	declare fs int;
	#	定义循环初始条件为true
	declare FOUND boolean default true;
	# 筛选出成绩大于60的
	declare sum_cur cursor for
		select studentid,courseid,grade
		from grade
		where grade>60;
	# 查询不到记录时，将FOUND置false
	declare continue handler for not found set FOUND = false;
	#初始的学分不能为空值，空值和任何值相加等于空值，必须初始化为0
	update 	student set sum_credit = 0;
	
	open sum_cur;
	fetch sum_cur into sid,cid,fs;
	while FOUND do
	# 将sum_credit值相加
		update student set sum_credit = sum_credit + (
			select credit
			from course
			where courseid = cid)
		where StudentID = sid;
		fetch sum_cur into sid,cid,fs;
	end while;
	close sum_cur;
	select * from student;
end;

call sum_credit();
select * from student;
drop procedure sum_credit;

题目二

create trigger t_s_gra before insert on grade for each row
begin
	if new.grade<0 or new.grade>100 then 
		signal SQLSTATE '45000' set message_text = "分数不合理",MYSQL_ERRNO = 1333;
	elseif new.grade<60 then set new.remark = 'NO';
	else update student set sum_credit = sum_credit + (
		select credit from course where courseid = new.courseid
		)	
	where studentid = new.studentid;
	end if;
end;

insert into grade(courseid,studentid,semester,grade) values("Dp010001","St0109010002",2,150);
insert into grade(courseid,studentid,semester,grade) values("Dp010002","St0111040001",2,88);
insert into grade(courseid,studentid,semester,grade) values("Dp020001","St0210010002",2,50);

select * from grade;
drop trigger t_s_gra;

题目三

create trigger t_s_gra before insert on grade for each row
begin
	if new.grade<0 OR new.grade>100 then signal SQLSTATE '45000'	
	set message_text = '分数不合理',MYSQL_ERRNO = 1333;
	elseif new.grade<60 then set new.remark = 'NO';
	else update student set sum_credit = sum_credit + (
		select credit
		from course
		where courseid = new.CourseID)	
	where studentid = new.studentid;
	end if;
end;

insert into grade(courseid,studentid,semester,grade) values("Dp010001","St0109010002",2,150);
insert into grade(courseid,studentid,semester,grade) values("Dp010001","St0109010002",2,80);
insert into grade(courseid,studentid,semester,grade) values("Dp020002","St0210010002",2,50);

select * from grade;
drop trigger t_s_gra;

select * from grade
where StudentID = "St0210010002"

题目四

create trigger t_s_grade before update on grade for each row
begin
	if new.grade<0 OR new.grade>100 then signal SQLSTATE '45000'	
	set message_text = '分数不合理',MYSQL_ERRNO = 1333;
	elseif old.grade>60 and new.grade<60 
	then 
		set new.remark = 'NO';
		update student set sum_credit = sum_credit-(
		select credit
		from course
		where courseid = new.CourseID
	)	where studentid = new.studentid;
	elseif old.grade<60 AND new.grade>=60 
	then
		update student set sum_credit = sum_credit+(
		select credit
		from course
		where courseid = new.CourseID
	)	where studentid = new.studentid;
	end if;
end;

select * from student;
select * from grade where StudentID = "St0210010005";
drop trigger t_s_grade;

update grade set grade = 50 where StudentID = 'St0210010005' and CourseID = 'Dp030001';
update grade set grade = 90 where StudentID = 'St0210010005' and CourseID = 'Dp020002';

实验三

3.综合存储过程和触发器完成这个需求：
一个老师修改成绩时，后台调用存储过程和触发器，综合完成：
（1）在修改成绩之前，首先需要查看该同学该课程的成绩是否已经在表里存在了；
（2）如果该同学课程成绩未被录入，提示成绩未录入；
（3）如果该同学课程成绩已录入，修改分数差值不超过20分，正常修改。
（4）如果该同学课程成绩已录入，修改分数差值超过20分，则修改完成后会将这条成绩信息录入成绩复核表（包括学生号，课程号，修改前成绩，修改后成绩）。
成绩复核表自己创建。

create table grade_copy(
	StudentID varchar(20) primary key comment '学号',
	CourseID varchar(20) comment '课程号',
	old_grade int comment '原来的成绩',
	new_grade int comment '修改后的成绩'
);

#创建该存储过程check_g通过输入进来的学号和课程号来输出他的分数
create procedure p_c_g(in sid varchar(20),in coid varchar(20),out fs int)
begin
	select grade into fs
	from grade
	where StudentID = sid
	and courseid = coid;
end;

#该触发器是对分数修改之后的操作，为了验证grade表中是否有学生成绩，没有就抛异常，有就直接修改成绩
create trigger t_gg  before update on grade for each row
begin
	declare f_s int;#定义临时变量来存放找到的学生成绩
	call p_c_g(new.StudentID,new.CourseID,@oldfs);
	if (@oldfs is null) then signal SQLSTATE '45000'	
	set message_text = '成绩未录入',MYSQL_ERRNO = 1334;
	else 
	# 用临时变量f_s去存放new.grade
	# 将grade当作一个变量来使用
	set f_s = new.grade;
	# 用临时变量f_s存放成绩，将值赋给new.grade
	set new.grade = f_s;
	end if;
end;



#以下是对分数修改之后的操作，分数差值大于20分就调用存储过程，将大于20分的学生学号和课程号传给存储过程copy
create trigger modify_t after update on grade for each row
begin
	if ABS(new.grade-old.grade)>20
		then call copy(new.StudentID,new.CourseID);
	end if;
end;


# 复制表内数据，将触发器中得到的学号和课程号来找出grade表中的新成绩，初始成绩为全局变量
create procedure copy(in sid varchar(20),in coid varchar(20))
begin
	# 存放新成绩
	declare new_grade int;
	select grade into new_grade from grade where StudentID = sid and CourseID = coid;
	insert into grade_copy values(sid,coid,@oldfs,new_grade);
end;

select * from grade where CourseID = "Dp030001"

select * from grade where studentid = "St0109010001"

update grade set grade = 60 where studentid = "St0109010001" and courseid = "Dp020001";

update grade set grade = 65 where studentid = "St0109010001" and courseid = "Dp010004";