--①选修成绩表SCTS中，学生选修成绩由平时成绩(regular_grade)和考试成绩(exam_grade)构成课程总评成绩(total_mark)，公式为：total_mark= regular_grade*30%+ exam_grade*70%
设计DML触发器，使得当用户修改某位学生选修某门课程的平时成绩或者考试成绩时，自动实现对该学生该门课程总评成绩的更新。
Create trigger Tri_UPDATE_SCTS on SCTS after UPDATE AS BEGIN IF UPDATE(regular_grade)or UPDATE(exam_grade) BEGIN DECLARE @rgrade float; DECLARE @egrade float; select @rgrade=regular_grade,@egrade= exam_grade from inserted update SCTS set total_mark=0.3*@rgrade+0.7*@egrade from inserted where SCTS.studentid =inserted.studentid and SCTS.courseid=inserted.courseid and SCTS.teacherid =inserted.teacherid END END |
--测试触发器
update Scts set regular_grade='100',exam_grade='100' where studentid='200520805403'and courseid='20224B0' and teacherid='080102' select *from scts where studentid='200520805403'and courseid='20224B0' and teacherid='080102' |
--2、②设计DML触发器，使得当某学生在一个学期中所选修的课程总学分超过20学分时，自动提示“你选修的总学分已达到最大值，不允许继续选课！”的信息；
Create trigger Tri_IN_LIMIT_SCTS on SCTS after insert --注意这里AFTER是插入之后的 AS BEGIN BEGIN DECLARE @allcredit float; DECLARE @sid varchar(12); select @sid =inserted.studentid from inserted select @allcredit=sum(credit) from courses where courseid in( select courseid from scts where studentid=@sid ) if (@allcredit>20) begin Rollback Transaction print @allcredit print'你选修的总学分已达到最大值，不允许继续选课！' end else print'选课成功' END END |
