存储过程,触发器,游标示例

table1结构如下

id    int
name  varchar(50)

--------------------------------------------------------------------------------------------

触发器:


例子,

create trigger person_tri on table1
for insert as
	insert into otherTable(id,name) values(1,'There trigger has been triggered');

触发 insert into table1 values(1,'the name');

--------------------------------------------------------------------------------------------
存储过程:


例子,

创建
create procedure person_pro
       @name varchar(4)[,@result output]
       as
       begin TRANSACTION
         select * [@result=name] from table1 where personName=@name
       COMMIT;

执行
[declare @result varchar(50)]
EXECUTE person_pro 'Tom'[,@result output]


--------------------------------------------------------------------------------------------
游标:


例子,

declare @id int
declare @name varchar(50)

declare cursor1 cursor for         --定义游标cursor1

select * from table1               --使用游标的对象(跟据需要填入select文)

open cursor1                       --打开游标

fetch next from cursor1 into @id,@name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中

while @@fetch_status=0           --判断是否成功获取数据
begin
update table1 set name=name+'1'
where id=@id                           --进行相应处理(跟据需要填入SQL文)
fetch next from cursor1 into @id,@name  --将游标向下移1行
end

close cursor1  --关闭游标

deallocate cursor1