今日小总结（触发器的应用与游标的创建）

-----------触发器---------------
--在每次插入数据的时候都把插入耳机数据值打印出来
--inserted表，after

-- 1在每次插入数据的时候都把插入的数据值打印出来。
create trigger tri_T_Class_after
on T_Class after insert
as
begin
  declare @cId int
  declare @cName varchar(50)
  declare @cDesc varchar(50)
 
  select @cId=Fclsid,@cName=FclsName,@cDesc=FclsDiecription from inserted
  print @cId
  print @cName
  print @cDesc
end
insert into T_Class values('Java','嗯')

drop trigger tri_T_Class_after

 

-- 2将删除的表中（Class）的数据备份到另外一个表中（ClassBak）
--1建一空表（ClassBak）与Class的结构一样
select top 0 * from ClassBak
from T_Class

--2创建触发器
create trigger tri_T_Class_Delete_after
on T_Class after Delete
as
begin
  set identity_insert ClassBak on
  insert into ClassBak(Fclsid,FclsName,FclsDiecription)
  select * from deleted
  set identity_insert ClassBak off
end

delete from T_Class
--删没了，还可以把它copy回去  因为备份在了ClassBak中了
set identity_insert T_Class on
insert into T_Class (Fclsid,FclsName,FclsDiecription)
seletc  * from CLassBak
set identity_Insert off
select * from T_Class

--  3--替换触发器(instead of)--替换删除操作
drop trigger tri_T_Class_Delete_insert_of
on T_Class instead of delete
as
begin
   set identity_insert ClassBak on
  insert into ClassBak(Fclsid,FclsName,Fclsdiscription)
  select * from deleted
  set identity_insert ClassBak off
end
delete from CLassBak
select * from CLassBak
select  * from T_Class
select * from T_Class where Fclsid=10

use TextSchool
select * from T_Student
--使用游标
--1游标的定义
--(1)定义
declare cur_MyStudent cursor fast_forward
for select * from T_Student  ---基于返回的结果集

--(2)打开
open cur_MyStudent
--(3)操作
fetch next from cur_T_Student
while @@FETCH_STATUS=0
begin
  fetch next from cur_T_Student
end
--(4)关闭
close cur_MyStudent
--(5)释放
deallocate cur_T_Student

select * from T_Teather