????--4?????DML??????????????????????????????????SCTS???в??????????????SCTS???е?regular_grade??exam_grade?????????????????仯??GRADE_LOG??student?? course?? teacher?? regular_grade?? exam_grade?? username?? userdate????????????????????????????????????????????(system_user)

 

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;

????--???3??
????update  Scts
????set regular_grade='100'??exam_grade='20'
????where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
????--?????????????????????
????update  Scts
????set total_mark='100'
????where studentid='200520805403'and courseid='20224B0' and teacherid='080102'
????--5??DDL????????????????Teaching??????е????????????????

CREATE TRIGGER TRI_Teaching_DDL
ON database
for alter_table??drop_table
AS
BEGIN
print '???????????????????'
Rollback Transaction
END