SQL解发器与SQL游标实例

--  一、触发器：
--  1在每次插入数据的时候都把插入的数据值打印出来。


     use MySchool
select * from Class


create trigger trigger_Class_insert  
on Class after insert
as
   begin
      declare @clsid int
      declare @cName varchar(50)
      declare @cDesc varchar(50)
      select @clsid=clsId,@cName=cName,@cDesc=cDescription from inserted
      print @clsid
      print @cName
      print @cDesc
      
   end
   
  insert into  Class values ('微机班','好班2')
    
--  2将删除的表中（Class）的数据备份到另外一个表中（ClassBak）
-- (1)建一个空表（ClassBak）与Class的结构一样。
  
     select top 0 * into ClassBak from Class
  
-- (2)创建触发器


     create trigger trigger_Class_Delete
     on Class after delete
     as
        begin
           set identity_insert ClassBak on
           insert into  ClassBak (clsId,cName,cDescription)
           select * from deleted
           set identity_insert ClassBak off
        end
   
     delete from Class
      select * from Class
      select * from ClassBak
--  3--替换触发器(instead of)--替换删除操作


     create trigger trigger_ClassBak_Instead_of
     on Class instead of delete
     as
     begin
         set identity_insert ClassBak on
          insert into ClassBak (clsId,cName,cDescription) 
          select * from deleted
         set identity_insert ClassBak off
     end
   
     delete from Class
     drop trigger trigger_ClassBak_Instead_of
--  二、游标
--1讲的工资更新，更新后的金额为原来的工资+奖金。


       create table TblTeacherSalary
(
autoId int identity(1,1) primary key,
tTId int not null,
reward money
)
insert into TblTeacherSalary values(1,1200)
insert into TblTeacherSalary values(2,2100)
insert into TblTeacherSalary values(3,800)
insert into TblTeacherSalary values(4,200)


select * into TblTeacher from teacher


select * from TblTeacher
select * from TblTeacherSalary
  
  declare @id int
  declare @Salary money
  declare cur_TblTeacherSalary cursor fast_forward
  for select tTId,reward from TblTeacherSalary
  open cur_TblTeacherSalary
     fetch next from cur_TblTeacherSalary into @id,@Salary
     while @@FETCH_STATUS=0
     begin
        update TblTeacher set tSalary+=@Salary where tTId=@id
          fetch next from cur_TblTeacherSalary into @id,@Salary
     end
  
  close cur_TblTeacherSalary
  deallocate cur_TblTeacherSalary
  
     
  
--  2将奖金中的reward设置为 工资的0.1倍
 
  select * from TblTeacher
  select * from TblTeacherSalary
  
  declare @id1 int
  declare @Salary1 money
  declare cur_TblTeacherSalary cursor forward_only
  for select tTId from TblTeacherSalary
  open cur_TblTeacherSalary
   fetch next from cur_TblTeacherSalary into @id1
   while @@FETCH_STATUS=0
      begin
       select @Salary1=tSalary from TblTeacher where tTId=@id1
        update TblTeacherSalary set reward=@Salary1*0.1 where CURRENT of 
        cur_TblTeacherSalary
        fetch next from cur_TblTeacherSalary into @id1
      end
  close cur_TblTeacherSalary
  deallocate cur_TblTeacherSalary


--  3把游标写在对面的表上第一题把游标写在TblTeacher.改TblTeacher；
     select * from TblTeacher
  select * from TblTeacherSalary
     
   declare @id2 int
   declare @Salary2 money
   declare cur_TblTeacher cursor forward_only
   for select tTId from TblTeacher
   open cur_TblTeacher
       fetch next from cur_TblTeacher into @id2
       while @@FETCH_STATUS=0
         begin
            select @Salary2=reward from TblTeacherSalary where tTId=@id2
            update TblTeacher set tSalary+=@Salary2 where current of cur_TblTeacher
             fetch next from cur_TblTeacher into @id2
         end
   close cur_TblTeacher
   deallocate cur_TblTeacher


--第二题改TblTeacherSalary,我们把游标写在techar上。


     select * from TblTeacher
  select * from TblTeacherSalary
   
  declare @id3 int
  declare @Salary3 money
  declare cur_TblTeacher cursor fast_forward
  for select tTId,tSalary from TblTeacher
  open cur_TblTeacher
     fetch next from cur_TblTeacher into @id3,@Salary3
      while @@FETCH_STATUS=0
       begin
         update TblTeacherSalary set reward=@Salary3*0.1 where tTId=@id3
         fetch next from cur_TblTeacher into @id3,@Salary3
       end
  close cur_TblTeacher
  deallocate cur_TblTeacher
  
--  4把奖金表中的奖金更新为 reward-teacher.salary*0.05


   select * from TblTeacher
   select * from TblTeacherSalary
   
   declare @id4 int
   declare @Salary4 money
   declare cur_TblTeacher cursor fast_forward
   for select tTId,tSalary from TblTeacher
   open cur_TblTeacher
      fetch next from cur_TblTeacher into @id4,@Salary4
      while @@FETCH_STATUS=0
        begin
            update TblTeacherSalary set reward-=@Salary4*0.05
            fetch next from  cur_TblTeacher into @id4,@Salary4
        end
   close cur_TblTeacher
   deallocate cur_TblTeacher