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

　　--更新成绩
　　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