目录
- 1.对sc表进行插入或修改时,如果考试成绩不在0-100范围内时,则撤销插入或修改操作。
- 2.对sc表进行插入时,如果学生的选课总学分超过30,则报错并撤销插入。
- 3.对sc表进行修改时,不允许修改考试成绩不及格的学生选课记录。
- 4.对student表进行插入时,如果年龄为空,则将该生年龄设置为所有学生年龄的平均值。
- 5.在student表中增加两列,分别用来存放学生的平均成绩和等级,并对sc表进行插入后,自动维护学生的平均成绩和等级(85-100,等级为“优”; 60-85,等级为“良”,60 以下,等级为“差”)
基于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 ;
总结
本篇文章就到这里了,希望能给你带来帮助,也希望您能够多多关注www.887551.com的更多内容!