Sql 练习题 （八）

--（1）创建后触发器每当用户在SC表中插入一行数据时，就显示一个提示信息。  

create trigger t1
on sc
after insert
as 
   print '向sc插入了一行数据';

   

--（2）创建后触发器限定Course表中Semester的取值范围为1-10。 

create trigger t2
on course 
after update,insert
as
   if exists(select * from inserted where Semester not between 1 and 10)
   rollback;

 
--（3）创建后触发器限制每个学期开设的课程总数不能超过10门，如果超过了10门，则给出提示信息：本学期课程太多。  

create trigger t3
on course 
after insert
as
    if(select COUNT(*) from Course c join inserted i on i.Semester=c.Semester) > 10
    begin
       print '本学期课程太多'
       rollback
    end;

--（4）创建后触发器在SC表中，不能将不及格的考试成绩改为及格。

create trigger t4
on sc
after update
as
    if exists(select * from inserted i join deleted d on i.Cno=d.Cno and i.Sno=d.Sno where i.Grade>=60 and d.Grade <60)
    begin
         print '不能将不及格的考试成绩改为合格'
         rollback
     end;

--（5）创建前触发器在SC表中，不能删除考试成绩不及格学生的该门课程的考试记录。

create trigger t5
on sc
instead of delete
as
    if not exists(select * from deleted where Grade<60)
    delete from SC where Sno in (select Sno from deleted ) and Cno in (select Cno from deleted);

--   适用于一次删除一条元组
CREATE Trigger tri_DeleteSC1
  ON SC INSTEAD OF DELETE
AS
  IF NOT EXISTS(SELECT * FROM DELETED   
                 WHERE Grade < 60 ) 
Begin
Select * from deleted
DELETE FROM SC 
    WHERE Sno IN( SELECT Sno FROM DELETED )
AND Cno IN( SELECT Cno FROM DELETED )
  End
  
--    一次删除多条元组
create Trigger tri_DeleteSC1
  ON SC INSTEAD OF DELETE
AS
  IF NOT EXISTS(SELECT * FROM DELETED   
                 WHERE Grade < 60 ) 
begin
select * from deleted
DELETE FROM SC 
from sc1 join deleted d on sc.sno=d.sno and sc.cno=d.cno
end

--   测试更新命令
delete from sc where sno='0811101' and cno='C001'
delete from sc where cno='C002' 
delete from sc where sno='0811103'
delete from sc where grade=80

--（6）删掉course表中的课程信息，请使用SQL语句做出相应处理（设系统对于参照完整性的策略为拒绝删除）。

create Trigger DEL_course
  ON Course INSTEAD OF delete
  AS
   -- if exists(select * from sc where cno in
    --(select cno from deleted))
    -- begin 
     delete from sc where cno in
     (select cno from deleted) 
    -- end        
    delete from course where cno in
     (select cno from deleted)

--测试更新命令
delete from course where cname='计算机网络'
delete from course where cname='Java'

--*（7）创建触发器防止在Students数据库中删除和更改任何表。

create trigger t7
on database
for drop_table ,alter_table
as
    print'不能在Students中删除或更改表!'
    rollback;