--测试触发器

 

select  sum(credit) '总分'
from courses
where courseid in(
select courseid
from scts
where studentid='200520701201'
)
insert into Scts(courseid，studentid，teacherid)
values ('50103Q0'，'200520701201'，'080102');
select *from courses where courseid='10042B0'

　　--3.设计DML触发器限定：对于“专业”（COURSES.character，包括专业课、专业基础、专业选修等）课程，只有该课程开课学院的学生才能选修，否则提示“不允许跨院选课！”的提示信息。

 

Create trigger Tri_INSERT_SCTS
on SCTS
after insert
AS
BEGIN
DECLARE @stype varchar(10);
DECLARE @collegeid varchar(5);
if exists(select * from courses AS C
where C.courseid in(select courseid  from inserted ) and C.Character like '专业%')
BEGIN
select @collegeid=college from courses AS C
where C.courseid in(select courseid  from inserted )
if exists (select college from students AS S
where S.studentid in(select studentid  from inserted )and S.college=@collegeid)
PRINT '选专业课成功'
else
BEGIN
Rollback Transaction
PRINT '不允许跨院选课！'
END
END
ELSE
PRINT '选修公共选修课成功'
END

　　--选修本学院专业课
　　insert into Scts(courseid，studentid，teacherid)
　　values ('20224B0'，'200520805403'，'080102');
　　--选修非本院专业课 终止
　　insert into Scts(courseid，studentid，teacherid)
　　values ('10019B5'，'200520805403'，'080102');
　　--选修公共选修课

 

insert into Scts(courseid，studentid，teacherid)
values ('50095Q0'，'200520805403'，'080102');
select *from students where college='08'
select *from colleges where collegeid='08'
select *from courses where courseid in (select courseid from courses where character not like '专业%')and college
='08'
select *from scts where studentid='200520805403'
delete from scts  where  courseid='20224B0' and  studentid='200520805403';