文章目录
触发器一、触发器的概述二、触发器的基本操作2.1 after和before2.1.1 afterdelete型update型insert型2.1.2 beforeinsert型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开始，可以为一张表定义具有相同触发事件和触发时机的多个触发器。默认情况下，具有相同触发事件和触发时机的触发器按其创建顺序激活。
触发时机表示数据表在发生变化前后的两种状态，触发事件表示激活触发器的操作类型，触发顺序表示指定在同一个表中多个触发器的执行顺序，默认情况下按创建顺序激活。
创建触发器的选项
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 rowdelete 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 rowupdate 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型
创建一个存储过程，根据student表中数据，一次性更新class表中每个班的人数create procedure p_c1() #901 #输入班级号begindeclare 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 doupdate 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();
创建一个触发器t_i_s，当student表插入新学生时，class表中该班级人数加1
create trigger t_i_s after insert on student for each rowupdate 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 rowbeginif 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 rowbegindeclare 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 rowbegin # 前面已经有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 thensignal 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");
四、实验题
实验一
定义一个触发器，为student表定义完整性规则“插入学生的信息时，性别只能为男或者女，如果输入除了男女之外的性别，则自动改为男”create trigger t_student before insert on student for each rowbeginif 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;#定义循环初始条件为truedeclare FOUND boolean default true;# 筛选出成绩大于60的declare sum_cur cursor forselect studentid,courseid,gradefrom gradewhere grade>60;# 查询不到记录时，将FOUND置falsedeclare continue handler for not found set FOUND = false;#初始的学分不能为空值，空值和任何值相加等于空值，必须初始化为0update 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 creditfrom coursewhere 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 rowbeginif 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 rowbeginif 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 creditfrom coursewhere 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 gradewhere StudentID = "St0210010002"
题目四
create trigger t_s_grade before update on grade for each rowbeginif 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 creditfrom coursewhere courseid = new.CourseID)where studentid = new.studentid;elseif old.grade<60 AND new.grade>=60 thenupdate student set sum_credit = sum_credit+(select creditfrom coursewhere 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)beginselect grade into fsfrom gradewhere StudentID = sidand courseid = coid;end;#该触发器是对分数修改之后的操作，为了验证grade表中是否有学生成绩，没有就抛异常，有就直接修改成绩create trigger t_gg before update on grade for each rowbegindeclare 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.gradeset new.grade = f_s;end if;end;#以下是对分数修改之后的操作，分数差值大于20分就调用存储过程，将大于20分的学生学号和课程号传给存储过程copycreate trigger modify_t after update on grade for each rowbeginif ABS(new.grade-old.grade)>20then 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";
