????--?????????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