sql

---------20121114---------
--触发器
--在每次插入的时候把插入的数据打印出来。
--inserted表,aftre:在插入之后显示
create trigger tri_TblClass_after on TblClass after insert
as
begin
 declare @ClassId int
 declare @ClassName varchar(50)
 declare @ClassDesc varchar(50)
 select @ClassId=ClassId,@ClassDesc=ClassDesc,@ClassName=ClassName from inserted
print @classId
print @ClassName
print @ClassDesc
end
drop trigger tri_TblClass_after--删除触发器
insert into TblClass values ('java','哈哈哈')

--将删除的表（TblClass）中的数据备份到令一个表（TblClassBak）中
--1建一个空表
select top 0 * into ClassBak from TblClass
select * from ClassBak
---2.创建触发器
create trigger tri_TblClass_Delete_after
on TblClass after Delete
as
begin
 set identity_insert ClassBak on
 insert into ClassBak(ClassId,ClassName,ClassDesc)
 select * from deleted
 set identity_insert ClassBak off
end
delete TblClass where ClassId=7
select * from ClassBak

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

create trigger tri_TblClass_Delete_instead_of
on TblClass instead of delete
as
begin
 set identity_insert ClassBak on
 insert into ClassBak(ClassId,ClassName,ClassDesc)
 select * from deleted
 set identity_insert ClassBak off
end
delete from deleted
select * from ClassBak
select * from TblClass
delete from TblClass where ClassId<6
delete from ClassBak

--游标==
select * from TblStudent
--(1)定义游标
declare cur_TblStudent cursor fast_forward
for select * from TblStudent--基于返回的结果集
--(2)打开游标
open cur_TblStudent
--(3)操作
--fetch next from cur_TblStudent--取出一条记录
while @@fetch_status=0---表示上次游标移动成功
begin
 fetch next from cur_TblStudent
end
--(4)关闭
close cur_TblStudent
--(5)释放
deallocate cur_TblStudent

---1将老师的工资更新，更新后的金额为原来的工资+—奖金
--第一，定义两个变量，来存放TblTeacherSalary表中的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
 fetch next from cur_Reward into @id,@reward
end
close cur_Reward
deallocate cur_Reward
--2将奖金设置为工资的0.1被
declare @id int
declare @Salary money
declare cur_TblTeacher cursor fast_forward
for select tTId,tTSalary from TblTeacher
open cur_TblTeacher
fetch next from cur_TblTeacher into @id,@Salary
while @@fetch_status=0
begin
 update TblTeacherSalary set reward=@Salary*0.1 where tTId=@id
 fetch next from cur_TblTeacher into @id,@Salary
end
close cur_TblTeacher
deallocate cur_TblTeacher