ZK数据库实验十(T-SQL中的触发器)

1. 在表Student中建立删除触发器，实现表Student和表SC的级联删除，也就是只要删除表Student中的元组学号为s1，则表SC中SNO为s1的元组也要删除；建立完触发器后删除Student中学号为‘00030’的元组，看看表SC中SNO为‘00030’的选课记录是否也一起删除；

   create trigger s_delete
   on student
   instead of delete
   as begin
   delete from sc
   where sno in(select sno from deleted)
   delete from student
   where sno in(select sno from deleted)
   end
   delete from student where Sno = '00030'
   

2. 在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，实现规定年龄24岁以上（包括24岁）的学生才能选修职业规划这门课程，如果年龄小于24岁，则输出’年龄小于24，不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00001’,’005’,null)和(‘00005’,’005’,null)看看结果,试试使用一条SQL语句插入(‘00012’,’005’,null)和(‘00013’,’005’,null)看看结果，如果两行数据都插入不成功说明原因，并通过修改触发器实现一条SQL语句插入(‘00012’,’005’,null)和 (‘00013’,’005’,null)时，(‘00013’,’005’,null)数据插入成功；

   create trigger q2
   on sc
   for insert
   as begin
   	declare @id char(5)
   	select @id = sno from inserted
   	if((select cno from inserted) = '005' and (select sage from Student where Sno = @id)<24)
   	begin
   	print '年龄小于24，不能选修该门课程'
   	rollback transaction
   	end
   	else
   	print 'nice!'
   	end
   insert into course values('005','职业规划','4','0014')
   insert into sc values('00001','005',null)
   insert into sc values('00005','005',null)
   select * from sc
   

3. 在表SC中建立更改触发器，实现表SC中的修改后的成绩不能低于修改前的成绩，如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩比修改前低，不能修改’，修改失败，用update语句把学号为00001，课程号为001的成绩分别改为90和70，看看结果,试试用update语句把课程号为001的成绩都改为70，看看结果，要求触发器能够实现001号课程低于70分的成绩都能修改成为70，高于70分的成绩都不修改；

   create trigger q3 
   on sc
   after update
   as
   if(update(score))
   begin
   	declare @score1 numeric(3,1),@score2 numeric (3,1)
   	select @score1 = score from deleted
   	select @score2 = score from deleted
   	if(@score1 > @score2)
   	print 'nice!'
   else
   update sc
   set sc.Score = @score2 from sc , deleted
   where sc.SNo = deleted.SNo and sc.CNo = deleted.CNo
   print '修改后的成绩比修改前低，不能修改'
   end
   update sc
   set Score = 90 where SNo = '00001'
   update sc
   set Score = 70 where CNo = '001'
   

4. 在表Teacher中创建触发器，实现如果更新了表Teacher中的年龄和工资，则输出’更新了年龄和工资’，如果更新了年龄没有更新工资，则输出’更新了年龄’，如果更新了工资而没有更新年龄，则输出’更新了工资’，创建完后使用SQL语句把tno为001的年龄加1，把tno为002的工资加1，把tno为003的年龄和工资都加1，看看结果；

   create trigger q04 on teacher
   after update
   as begin
   declare @age int,@sal float
   select @age = age from deleted
   select @sal = sal from deleted
   if(@age<>(select age from inserted) and @sal<> (select sal from inserted))
   print'更新了年龄和工资'
   else if(@age<>(select age from inserted) and @sal=(select sal from inserted))
   print'更新了工资'
   else if(@age=(select age from inserted) and @sal<> (select sal from inserted))
   print'更新了年龄'
   end
   update teacher 
   set age = age+1 where Tno = '0001'
   update teacher
   set sal = sal+1 where Tno = '0002'
   update teacher
   set age = age+1 where Tno = '0003'
   update teacher
   set sal = sal+1 where Tno = '0003'
   

5. 在不删除触发器的前提下，使题3创建的触发器无效；

   alter table teacher disable trigger q04

6. 创建一个名为tri_Delete_C的触发器，要求首先判断数据库中是否已经存在名为tri_Delete_C的触发器，如果存在，首先删除，再创建，触发器要求删除一门课程时候，首先判断该课程有否有人选，如果有人选，则不能删除,并通过测试数据验证该触发器的执行情况。

   if(exists (select * from  sysobjects where xtype='tr' and name='tri_Delete_C'))
   begin
     drop trigger tri_Delete_C
     print '已删除'
     end
     go
     create trigger tri_Delete_C on course
      for delete
       as 
    begin
    if exists (select * from sc inner join deleted on sc.CNo=deleted.CNo)
    begin
      print '不能删除'
      rollback transaction
    end
    end