????--?????????

 

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';