11-14笔记

2012-11-14
----触发器
------在每次插入数据的时候把插入的数据值打印出来
----inserted表，after.
create trigger tri_Class_after
on Class after insert
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

inserted into Class values ('java','呵呵')

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

select * from ClassBak
---2创建触发器
create trigger tri_Class_Delect_after
on Class after Delect
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 where clsId='10'

 

set identity_insert Class on
insert into Class (clsId,cName,cDescription)
select * from ClassBak
set identity_insert Class off

---替换触发器(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
insert into ClassBak select * from deleted
set identity_insert ClassBak off
end
delete from ClassBak
select * from ClassBak
select * from Class
selete * from Class where clsId=10

use TestSchool
select * from TblTeacher

 

---使用游标
---1游标的定义
--（1）定义
declare cur_MyStudent cursor fast_forword
for select * from MyStudent --基于返回一个结果集
--（2）打开
open cur_MyStudent
--(3)操作
fetch next from cur_MyStudent
while @@fetch_status=0   ---while循环说的是多条记录的操作
begin
fetch next from cur_MyStudent
end
--(4)关闭
close cur_MyStudent
--(5)释放
deallocate

 

select * from TblTeacher
select * from TblteacherSalary

------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 wheretTId=@id
 fetch next from cur_Reward into @id,@reward
 end
close cur_Reward
deallocate cur_Reward

--2将奖金中的reward设置为工资的0.1倍

declare @sid int
declare @salary money
--1定义游标
declare cur_reward2 cursor fast_forward
for select tTId from TblTeacherSalary
--打开
open cur_reward2
fetche next from cur_reward2 into @SId
while @@fetch_status=0
begin
--set @salary=(select tTSalary from TblTeacher)
select @salary=tTSalary from TblTeacher
--更新奖金表
update TblTeacherSalary set reward=@salary*0.1 wheretTId=@sid
end
close cur_reward2
deallocate cur_reward2