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