SQL9：触发器和游标

--触发器
--在保存数据时,完成备份
select * from TblOrders

create trigger bak_order
on tblorders
after insert
as
begin
insert into tblOrders_bak(oname,ocount,oprice)
select oname,ocount,oprice from inserted
end

alter trigger update_order
on tblorders
instead of update
as
begin
update tblOrders_bak set ocount=(select ocount from inserted)
where oname in (select oname from inserted)
end

select * from TblOrders
where oname='ff'
select * from tblOrders_bak
where oname='ff'

drop trigger update_order

insert into TblOrders values('ff',10,20)

update TblOrders set ocount=20 where oname='ff'

-》触发器：
当有关联操作发生的时候
比如：下订单时，创建中的商品数量需要减少
比如：退票时，总的票量要增加
属于DDL
关键字trigger
关键：确定要将触发器建立在哪个表上on
执行时间：after：在触发源执行完成后执行触发器中的代码
instead of：在触发源执行前执行触发器中的代码，同时触发源的操作被废弃
触发源：insert update delete
临时表：inserted deleted

----============游标
--游标的定义
declare s1 cursor for select oname,ocount,oprice from tblorders
--在访问游标中某行的数据时,需要声明变量
declare @name nvarchar(10)
declare @count int
declare @price money
--打开游标
open s1
--通过游标从结果集中取数据,注意:每次取一行的值
fetch s1 into @name,@count,@price
while(@@FETCH_STATUS=0)
begin
set @price=@price+RAND(100)
update TblOrders set oprice=@price where oname=@name and ocount=@count
fetch s1 into @name,@count,@price
end
--关闭游标
close s1
--释放游标
deallocate s1