触发器的问题,解决后马上给分..........很急!!!!!!!!!!!!!!!!!!!!!!!!

触发器执行结果正确,但是触发器里UPDATE过的表反应迟钝,大概要一分钟左右才能打开,好像是死锁的症状.打开后所有数据的更新全部达到了预期效果

请问这是为什么,本人很少写触发器,请高手指点

下面是po_receipt_goods的UPDATE触发器
主要意图是:当po_receipt_goods.state = '1' And po_receipt_goods.unok_yes = '0' 那么执行相关内容,更改po_buy_details.RECEIPT_QTY和 bs_lot.unit_qty

CREATE trigger tr_u_receipt_goods on dbo.po_receipt_goods
for update

as
declare @company_id char(10),
@receipt_id char(12),
@state char(1),
@unok_yes char(1)
Select @company_id = company_id,
@receipt_id = id,
@state = state,
@unok_yes=unok_yes from inserted

begin
if @state='1' and @unok_yes='0'
begin
--------------------------------------------------------
DECLARE @rec_state char(1),
@goods_id char(10),
        @rec_line_num int,
@rec_receipt_qty decimal(14,4),
@po_id char(12),
@po_line_num int,
@vendor_id char(10),
@lot_id char(10)
   SELECT @rec_state = state
from po_receipt_goods
WHERE company_id = @company_id And id = @receipt_id
If @rec_state = '1'
   BEGIN
DECLARE cur_receipt_dtl CURSOR FOR
SELECT po_receipt_details.goods_id,
          po_receipt_details.line_num,
       po_receipt_details.receipt_qty,
       po_receipt_details.lot_id,
               po_receipt_details.po_id,
       po_receipt_details.po_line_num,
               po_receipt_goods.vendor_id
FROM po_receipt_details INNER JOIN
      po_receipt_goods ON
      po_receipt_details.company_id = po_receipt_goods.company_id AND
      po_receipt_details.id = po_receipt_goods.id
WHERE po_receipt_details.company_id = @company_id And po_receipt_details.id = @receipt_id
BEGIN TRANSACTION TR_POST_RECEIPT
OPEN cur_receipt_dtl
FETCH NEXT FROM cur_receipt_dtl INTO @goods_id,@rec_line_num,@rec_receipt_qty,@lot_id,@po_id,@po_line_num,@vendor_id
WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE po_buy_details SET receipt_qty = receipt_qty + @rec_receipt_qty
   WHERE company_id = @company_id And id = @po_id And line_num = @po_line_num
           if @@rowcount=0
begin
   ROLLBACK TRANSACTION TR_POST_RECEIPT
end
   UPDATE bs_lot SET unit_qty = unit_qty + @rec_receipt_qty
   WHERE company_id = @company_id And id = @goods_id And lot_id = @lot_id And vendor_id = @vendor_id
   if @@rowcount=0
begin
   ROLLBACK TRANSACTION TR_POST_RECEIPT
end
   FETCH NEXT FROM cur_receipt_dtl INTO @goods_id,@rec_line_num,@rec_receipt_qty,@lot_id,@po_id,@po_line_num,@vendor_id
END

CLOSE cur_receipt_dtl
DEALLOCATE cur_receipt_dtl
      COMMIT TRANSACTION TR_POST_RECEIPT
   END
ELSE
   BEGIN
ROLLBACK TRANSACTION TR_POST_RECEIPT
   END

-------------------------------------------------------------------
end
END

5 个解决方案

#1

呵呵，好长哦

#2

请在更新时不要用游标。

#3

如,应该采用这种方法,具体的,你自己修改

Update po_buy_details Set receipt_qty = Isnull(receipt_qty,0) +T.receipt_qty From(SELECT po_receipt_details.goods_id,
          po_receipt_details.line_num,
       po_receipt_details.receipt_qty,
       po_receipt_details.lot_id,
               po_receipt_details.po_id,
       po_receipt_details.po_line_num,
               po_receipt_goods.vendor_id
FROM po_receipt_details INNER JOIN
      po_receipt_goods ON
      po_receipt_details.company_id = po_receipt_goods.company_id AND
      po_receipt_details.id = po_receipt_goods.id
WHERE po_receipt_details.company_id = @company_id And po_receipt_details.id = @receipt_id)T
WHERE po_buy_details.company_id = @company_id And po_buy_details.id = @po_id And line_num = @po_line_num

#4

你的触发器中使用游标，当然慢了

#5

TO: Frewin(Frewin)
如果在旧社会我一定叫你一声"老大"
现在只能说,大哥,太感谢了,

#1