sql service 游标和触发器的使用

 一、 定义游标

           使用游标相当于C#里面的集合。

declare @id nvarchar(20)
 DECLARE My_Cursor CURSOR --定义游标
FOR (select autoid from U_VoucherItems where CardNum='k006' and CardSection='B') --查出需要的集合放到游标中 
OPEN My_Cursor; --打开游标
 FETCH NEXT FROM My_Cursor  INTO @id; --读取第一行数据
 WHILE @@FETCH_STATUS = 0
 BEGIN      
     update U_VoucherItems set CardItemNum=(select MAX(CardItemNum)+1 from U_VoucherItems where CardNum='k006' and CardSection='B'
     ) where CardNum='k006' and autoid=@id
 
 FETCH NEXT FROM My_Cursor  INTO @id; --读取第一行数据
 
    END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标

 

二、触发器和游标一起使用

例子

/****** Object:  Trigger [dbo].[tgr_changeprice_delete]    Script Date: 03/25/2016 11:33:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   Create   trigger [dbo].[tgr_changeprice_delete]
 on [dbo].[SA_SaleDelivery_b]  
 instead of delete  
 as
 declare @s_jsQuantity nvarchar(100),@zQuantity nvarchar(100),@s_wjsQuantity nvarchar(100),
 @ClearingMoney nvarchar(100),@yxQuantity nvarchar(100),@NotClearingMoney nvarchar(100),
 @Price nvarchar(100),@changepricedetailerid nvarchar(100); --定义变量    
     DECLARE My_Cursor CURSOR --定义游标
     FOR (select quantity2,quantity,OrigDiscountPrice,sourceVoucherDetailId from deleted) --查出需要的集合放到游标中 
     OPEN My_Cursor; --打开游标
     FETCH NEXT FROM My_Cursor  INTO @zQuantity,@s_jsQuantity,@Price,@changepricedetailerid; --读取第一行数据
       if(@changepricedetailerid is not null)   --判断
       begin 
     print 'start......'
     WHILE @@FETCH_STATUS = 0
     BEGIN
        set @s_wjsQuantity=CONVERT (decimal(19,2),@s_jsQuantity);
        set @ClearingMoney=CONVERT (decimal(19,2),@Price)*CONVERT (decimal(19,2),@s_jsQuantity);
        set @NotClearingMoney=CONVERT (decimal(19,2),@Price)*CONVERT (decimal(19,2),@s_wjsQuantity);
  
  update nsc_changeprice_b set 
  yxQuantity= yxQuantity+CONVERT (decimal(19,2),@s_jsQuantity),
  jsQuantity=jsQuantity-CONVERT (decimal(19,2),@s_jsQuantity),
  wjsQuantity=wjsQuantity+CONVERT (decimal(19,2),@s_wjsQuantity),
  ClearingMoney=ClearingMoney-CONVERT (decimal(19,2),@ClearingMoney),
  NotClearingMoney=NotClearingMoney+CONVERT (decimal(19,2),@NotClearingMoney)
   where id=@changepricedetailerid 
   FETCH NEXT FROM My_Cursor  INTO @zQuantity,@s_jsQuantity,@Price,@changepricedetailerid; --读取第一行数据
   END
   CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
   end
     
    else
    begin 
    print 'end.....'
    --rollback transaction --回滚﹐避免加入 
    end