---
title: "触发器和游标"
author: "mywebcode"
date: "2012-11-14T20:56:00Z"
tags: []
slug: "untitled"
draft: false
lastmod: "2013-05-08T10:26:28Z"
id: 1862864
url: "https://mywebcode.iteye.com/blog/1862864"
---

1：触发器  
触发时自动生成两个表inserted和deleted  
定义：类似于C#中的事件，是一种特殊的参数不用手动调用；  
语法：create trigger 触发器名 on设置触发器的表 after（for）或 instead of 查询语句类型

（insert，delete，updata）  
as  
begin  
触发时要做的事  
end  
after在执行表（iserted/deleted）中有记录，在被执行记录表中没记录;而instead of则会替换触发器

不让其执行执行删除时量表中都会有同条记录  
例子：  
\-- 1在每次插入数据的时候都把插入的数据值打印出来。  
create trigger trigger\_print on teacher  
after insert  
as  
begin  
declare @tname nvarchar(50)  
declare @tsalary money  
select @tname= tname from inserted  
select @tsalary= tsalary from inserted  
end

print @tname  
print @tsalary  
insert into teacher values('钟莲',8000)  
将deleted表备份到另一个表  
select top 0 * into classbak  
from teacher  
\-- --(2)创建触发器  
create trigger trigger\_eu on teacher  
after delete  
as  
begin  
set identity\_insert classbak on--插主键时要记得打开  
insert into classbak (tid,tname,tsalary)--注意字段必须记得写  
select * from deleted  
set identity\_insert classbak off  
end  
delete from teacher where tid=1  
select * from classbak  
select * from teacher  
2：游标  
效率比较慢  
定义declare 游标名 cursor 游标选项（fast\_forward,forward\_only&lt;得到当前id current of 游标名

&gt;）  
for 查询语句（这里的表就是游标所建的表）  
（2）open  
（3）操作  
fetch next from 变量名  
while @@fetch\_status=0 --全局变量看上一条是否被执行（移动成功）  
begin  
sql语句  
fetch next from 变量名//改变条件的语句  
（4）关闭 close  
（5）释放 deallocate  
\-- 2将奖金中的reward设置为 工资的0.1倍  
\---1:定义变量  
declare @id int  
declare @salary money  
\--2：建游标在工资表上  
declare add\_reward cursor fast\_forward  
for select tid,tsalary from teacher  
open add\_reward  
fetch next from add\_reward into @id,@salary  
while @@FETCH\_STATUS=0  
begin  
update teachersalary set [reward=0.1\*@salary](mailto:reward=0.1*@salary)  
where [tsid=@id](mailto:tsid=@id)  
fetch next from add\_reward into @id,@salary  
end  
close add\_reward  
deallocate add\_reward  
end