触发器、游标的一些小练习

触发器

--1.建一个空表,与Class表结构一样
select top 0 * into ClassBak from Class

--2.建触发器
create trigger tri_Class_Delete_after2
on Class after Delete
as
begin
set IDENTITY_INSERT ClassBak on
insert into ClassBak (FclsId,FclsName,FclsDisctription)
select * from deleted
set IDENTITY_INSERT ClassBak off
end

--删除表之后，已经备份到另外一个表中了,可以对其进行还原
set IDENTITY_INSERT Class on
insert into Class(FclsId,FclsName,FclsDisctription)
select * from ClassBak
set IDENTITY_INSERT Class off

--替换触发器(instead of)--替换删除操作
drop trigger tri_Class_Delete_after2
create trigger tri_Class_Delete_after2
on Class instead of delete
as
begin
set IDENTITY_INSERT ClassBak on
insert into ClassBak(FclsId,FclsName,FclsDisctription)
select * from deleted
set IDENTITY_INSERT ClassBak off
end

--1.游标的定义
--（1）定义
declare cur_Mystudents cursor fast_forward--游标的种类
for select * from Mystudents--基于返回的结果集
--（2）打开
open cur_Mystudents
--（3）操作
fetch next from cur_Mystudents
while @@fetch_status=0
 begin
 fetch next from cur_Mystudents
 end
--（4）关闭
close cur_MyStudents
--(5)释放游标
deallocate cur_Mystudents