数据库触发器控制

--①选修成绩表SCTS中，学生选修成绩由平时成绩(regular_grade)和考试成绩(exam_grade)构成课程总评成绩(total_mark)，公式为：total_mark= regular_grade*30%+ exam_grade*70%
设计DML触发器，使得当用户修改某位学生选修某门课程的平时成绩或者考试成绩时，自动实现对该学生该门课程总评成绩的更新。


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' 


--2、②设计DML触发器，使得当某学生在一个学期中所选修的课程总学分超过20学分时，自动提示“你选修的总学分已达到最大值，不允许继续选课！”的信息；
Create trigger Tri_IN_LIMIT_SCTS
on SCTS
after insert --注意这里AFTER是插入之后的
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'


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


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