一篇文章带你深入了解Mysql触发器

基于school数据库中的student, course, sc表,创建满足下述要求的触发器:

数据库navicat cmd界面运行

1.对SC表进行插入或修改时，如果考试成绩不在0-100范围内时，则撤销插入或修改操作。

				?

									DELIMITER ;

									DROP TRIGGER IF EXISTS `tri_update_grade`;

									DELIMITER ;;

									CREATE TRIGGER `tri_update_grade` BEFORE UPDATE ON `sc` FOR EACH ROW begin

									if new.grade > 100 then

									delete from sc;

									elseif new.grade < 0 then

									delete from sc;

									end if;

									end

									;;

				?

									DELIMITER ;

									DROP TRIGGER IF EXISTS `tri_insert_grade`;

									DELIMITER ;;

									CREATE TRIGGER `tri_insert_grade` BEFORE INSERT ON `sc` FOR EACH ROW begin

									if new.grade > 100 then

									delete from sc;

									elseif new.grade < 0 then

									delete from sc;

									end if;

									end

									;;

2.对SC表进行插入时，如果学生的选课总学分超过30,则报错并撤销插入。

				?

									DELIMITER ;

									DROP TRIGGER IF EXISTS `tri_insert_credit`;

									DELIMITER ;;

									CREATE TRIGGER `tri_insert_credit` BEFORE INSERT ON `sc` FOR EACH ROW begin

									SELECT sum(Ccredit) INTO @sumcred from Student join SC on SC.Sno=Student.Sno join Course on Course.Cno=SC.Cno WHERE sc.Sno=new.Sno;

									if (@sumcred>30)

									then

									delete from sc;

									end if;

									end

									;;

3.对SC表进行修改时，不允许修改考试成绩不及格的学生选课记录。

				?

									DELIMITER ;

									DROP TRIGGER IF EXISTS `tri_grade`;

									DELIMITER ;;

									CREATE TRIGGER `tri_grade` BEFORE UPDATE ON `sc` FOR EACH ROW begin

									select grade INTO @gra from sc where sno=new.sno and sno=new.sno;

									if (@gra >= 60)

									then

									set new.grade=20;

									else

									delete from sc;

									end if;

									end

									;;

4.对STUDENT表进行插入时，如果年龄为空，则将该生年龄设置为所有学生年龄的平均值。

				?

									DELIMITER ;

									DROP TRIGGER IF EXISTS `tri_student`;

									DELIMITER ;;

									CREATE TRIGGER `tri_student` BEFORE INSERT ON `student` FOR EACH ROW begin

									select avg(Sage) INTO @avgage from student;

									SET @old_age = new.Sage;

									if @old_age IS NULL

									then

									set new.Sage=@avgage;

									end if;

									end

									;;

5.在STUDENT表中增加两列，分别用来存放学生的平均成绩和等级，并对SC表进行插入后,自动维护学生的平均成绩和等级(85-100,等级为“优"; 60-85,等级为“良”，60 以下，等级为“差")

				?

									DELIMITER ;

									DROP TRIGGER IF EXISTS `tri_comment`;

									DELIMITER ;;

									CREATE TRIGGER `tri_comment` BEFORE UPDATE ON `student` FOR EACH ROW begin

									select avg(Grade) INTO @avggra from sc where sno=new.sno;

									if @avggra > 85 then

									update sc set new.Savg=@avggra,new.Scomment="you";

									else if @avggra > 60 and @avggra <= 85 then

									update sc set new.Savg=@avggra,new.Scomment="you";

									else

									update sc set new.Savg=@avggra,new.Scomment='cha';

									end if;

									end if;

									end

									;;

									DELIMITER ;