数据库实验—触发器

---

• 为Students创建一触发器，并给出正反实例的SQL语句：当插入或修改一条学生记录时，确保此学生的入学年龄在14~40岁之间。

  create trigger TRIGGER_Stu
  on Students
  for insert , update
  as
  declare @age int
  select @age=year(getdate())-year(Sbirth) from inserted
  if @age not between 14 and 40
  begin
  print '不符合年龄条件'
  rollback transaction
  end
  

• 为Teachers表创建一触发器，并给出正反实例的SQL语句：男职工年龄不能超过60岁，女职工职称是“教授”的年龄不能超过60岁，其他女职工年龄不能超过55岁

  create trigger trigger_teach
  on Teachers
  for insert
  as
  declare @age int
  declare @gender varchar(2)
  declare @prof varchar(20)
  select @age=(year(getdate())-year(Tbirth)),
  @gender=Tgender,@prof=Tprof
  from inserted
  if @gender = '男' or (@gender='女' and @prof='教授')
  begin
  if @age > 60
  --begin
  print '超龄'
  rollback transaction
  --end
  end
  else
  if(@gender = '女')
  begin
  if(@age>55)
  --begin
  print '超龄'
  rollback transaction
  --end
  end
  

• 为Teachers创建一触发器，并给出正反实例的SQL语句：当职称从“讲师”晋升为“副教授”时，岗位津贴（TComm）自动增加500元；从“副教授”晋升为“教授”时，岗位津贴自动增加900元；不允许越级晋升，也不能降级。

  create trigger trigger_teacher_prof
  on Teachers
  for update
  as
  declare @tno varchar(20)
  declare @up_prof varchar(20)
  declare @old_prof varchar(20)
  select @up_prof=Tprof,@tno=Tno from inserted
  select @old_prof from deleted
  if(@old_prof='讲师' and @up_prof='副教授')
  begin
  update Teachers set Tcomm=Tcomm+500 where Tno=@tno
  end
  else
  if(@old_prof='副教授' and @up_prof='教授')
  begin
  update Teachers set Tcomm=Tcomm+900 where Tno=@tno
  end
  else 
  if ((@old_prof='讲师' and @up_prof in ('讲师','副教授')) 
  or (@old_prof='助教' and @up_prof in ('助教','讲师')))
  begin
  print '不允许越级晋升'
  rollback transaction
  end
  

• 为Reports表创建一触发器，并给出正反实例的SQL语句：当插入一条记录或修改成绩时，若记录不为空，则须确保此记录的成绩在0~100分之间

  create trigger TRIGGER_Rpt
  on Reports
  for insert,update
  as
  declare @grade decimal(5,2)
  select @grade=Grade from inserted
  if(@grade not between 0 and 100)
  begin
  print '成绩插入有误'
  rollback transaction
  end
  

• 为选课表Reports增加一个属性列Level，并为该表创建一触发器，将成绩Grade按下列对应关系由分数转换成等级，并保存到Level列中：
  小于60分：不及格 60~70分：及格
  70 ~ 80分：中 80 ~ 90分：良
  90分以上：优

  alter table Reports add Re_Level varchar(10)
  

  • 如果直接使用语句设置变量值的话需要加set关键字

  create trigger TRIGGER_Rep
  on Reports
  for insert , update
  as
  declare @grade decimal(4,1)
  declare @level varchar(10)
  select @grade=Grade from inserted
  if(@grade between 60 and 70)
  begin 
  set @level='及格'
  end 
  else
  if(@grade between 60 and 70)
  begin 
  set @level='优'
  end 
  else
  begin 
  set @level='不及格'
  end 
  update Reports set Re_Level=@level
  

• 创建一个学分表StuCredits，包含两个属性列（学号Sno，总学分TotalCredits），用来保存学生已获得的总学分数。
  为选课表Reports创建一触发器：当增加一条记录或修改成绩Grade时，重新统计该名学生获得的总学分，并将结果保存到StuCredits表中
  ① 当StuCredits表为空时，更新Reports表中的某条选课记录的成绩，然后查询StuCredits表中的数据；
  ② 当StuCredits表不为空时，选择该表中已有的某位学生，然后在Reports表中新增一条该学生的选课记录，并给出成绩（>=60），然后再查询StuCredits表中，该为学生总学分的变化。

  create table StuCredits(
  	Sno char(12) primary key,
  	TotalCredits int
  )
  

  create trigger reports_credits
  on Reports
  for insert,update
  as
  declare @sno char(12)
  declare @credit int
  select @sno=Sno from inserted
  select @credit=sum(C.Ccredit)
  from Reports R, Courses C
  where R.Cno = C.Cno and R.Sno = @sno and R.Grade > 60
  if(not exists(select * from StuCredits where Sno = @sno))
  begin
  insert into StuCredits values(@sno,isnull(@credit,0))
  end
  else 
  begin
  update StuCredits set TotalCredits = isnull(@credit,0)
  where Sno=@sno
  end