触发器、游标

--触发器：是一种特殊类型的存储过程，主要是通过事件进行触发而被执行
作用：自动化操作

inserted表与deleted表——临时表
当你删除数据的时候，还有更新数据的时候，这时候在数据库中就会有deleted表，在这个表中会保存删除或更新的数据。在删除的同时，把删除的数据备份到了deleted中。

create trigger triggerName on 表名
after(for)（for与after都表示after触发器）|instead of|update|insert|delete（intert）

DML 触发器
-----------------------
--在每次插入数据的时候都把插入的数据值打印出来。inserted表，after(插入数据之后)
use School
select * from Class

create trigger tri_Class_after2
on Class after intert
as
begin
  declare @cId int
  declare @cName varchar(50)
  declare @cDesc varchar(50)
  select @cId=clsId,@cName=cName,@cDesc=cDescription from inserted
  print @cId
  print @cName
  print @cDesc
end

insert into Class values('java',' 嘿嘿')
--删除触发器
 drop trigger tri_Class_after2
--将删除的表中的数据备份到另外的一个表中（ClassBak）
--1.建表结构复制表结构(建一个表ClassBak与Class的结构一样)
select top 0 * into ClassBak
from Class
select *from ClassBak
--2创建触发器
create trigger tri_Class_Delete_after
on Class after intert
as
bengin
 set identity_insert ClassBak on
 intert into ClassBak(clsId,cName,cDescription)
 select * from deleted
 set identity_intert ClassBak off
end

drop trigger tri_Class_Delete_after
删除了，但备份在ClassBak里了，还可copy回去
delete from Class
 set identity_insert Class on
 intert into ClassBak(clsId,cName,cDescription)
 select * from deleted
 set identity_intert Class off
select * from Class

--替换触发器(instead of),替换删除操作
drop trigger tri_Class_Delete_after2

create trigger tri_Class_Delete_instead_of
on Class instead of delete
as
begin
 set identity_insert ClassBak on
 intert into ClassBak(clsId,cName,cDescription)
 select * from deleted
 set identity_intert ClassBak off
end

delete from ClassBak
select *from ClassBak
select *from Class
delete from Class  where clsId=10
------------------------------------------------------------------------------
游标是过程化思维，而SQL本身是集合化思维

use School
select * from MyStudent
--使用游标
1.定义游标
declare cur_MyStudent cursor fast_forward
for select * from MyStudent ---基于返回结果集
2.打开
open  cur_MyStudent
3.操作
fetch next from cur_MyStudent
while @@fetch_status=0
 begin
  fetch next from cur_MyStudent
 end
4.关闭
close cur_MyStudent
5.释放
deallocate cur_MyStudent

工具——选项——执行后放弃结合
工具SQL sever Picfiler
----------------------------------------
use TestShool
select * from TblTeacher
select * from TblTeachaeSalary
--1.将老师的工资更新，更新后的金额为原来的工资+奖金
    定义两个变量，来存放TblTeacherSalery表中的tTId和reward
declare @id int
declare @reward money
declare cur_Reward cursor fast_forward
for select tTId,reward from TblTeacherSalary
open cur_Reward
 fetch next from cur_Reward into @id,@reward
 while @@fetch_status=0
  begin
    update TblTeacher set tTSalary=tTSalary+@reward where tTId=@id
  end
close cur_Reward
deallocate cur_Reward
2.将奖金中的reward设置为工资的0.1倍
declare @sid int
declare @salary money
declare cur_Reward2 cursor fast_forward

open cur_Reward2
 fetch next from cur_Reward2 into @sid
 while @@fetch_status=0
  begin
   -- set @salary=(select tTsalary from TblTeacher)
   select @salary=select tTsalary from TblTeacher where tTId=@sid
   更新奖金表
   update TblTeacherSalary set reward=@salary*0.1 where current of cur_Reward2
   for select tTid from TblTeacherSalary
  end
close cur_Reward2
deallocate cur_Reward2
select * from TblTeacher
select * from TblTeachaeSalary
1.把游标写在对面的表上，把游标写在TblTeacher,改TblTeacher
2.改TblTeacherSalary，我们把游标写在techar上。
3.把奖金表中的奖金更新为reward-teacheer.salary*0.5
update TblTeacherSalary set reward=reward-(select tTSalary*0.5 from TblTeachar where TblTeachar.tTId=TblTeacherSalary.tTId)
--------------