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'

Create trigger Tri_IN_LIMIT_SCTS
on SCTS
after insert --×¢ÒâÕâÀïAFTERÊDzåÈëÖ®ºóµÄ
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

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'

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

Create table GRADE_LOG(
id int  identity(1,1) primary key,
student varchar(20) not null ,
course  varchar(50) not null,
teacher varchar(20) not null,
regular_grade float ,
exam_grade float,
username  varchar(20) not null,
userdate datetime not null,
operator varchar(10) not null
)
--select system_user  getdate()
Create trigger  Tri_IN_U_SCTS
on SCTS
after INSERT,UPDATE
AS
BEGIN
IF UPDATE(regular_grade)OR UPDATE(exam_grade)or (exists (select 1 from inserted) and not exists (select 1
from deleted))
BEGIN
DECLARE @student varchar(20);
DECLARE @course varchar(50);
DECLARE @teacher varchar(20);
DECLARE @rgrade float;
DECLARE @egrade float;
DECLARE @username  varchar(20);
DECLARE @date datetime;
DECLARE @type varchar(10);
select @type='update';
if exists (select 1 from inserted) and not exists (select 1 from deleted)
select @type='insert';
select @student=sname from students where studentid in(select studentid  from inserted )
select @course =cname from courses where courseid in (select courseid from inserted)
select @teacher =tname from teachers where teacherid in (select teacherid from inserted)
select @rgrade=regular_grade,@egrade=exam_grade from inserted
select @username=system_user,@date=getdate();
insert into GRADE_LOG
values(@student,@course,@teacher,@rgrade,@egrade,@username,@date,@type)
END
END
select * from  GRADE_LOG;

CREATE TRIGGER TRI_Teaching_DDL
ON database
for alter_table,drop_table
AS
BEGIN
print '²»ÔÊÐíÐ޸Ļòɾ³ýÊý¾Ý±í£¡'
Rollback Transaction
END