--①选修成绩表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