Trriger 范例

CREATE TRIGGER I_MKS_ERP_RCV_t ON [dbo].[I_MKS_ERP_RCV]
FOR INSERT
AS
declare @item_no nvarchar(50),@rcv_qty float,@plan_qty float,@id int,@plan_qty_h float
begin
 select @item_no=item_no,@rcv_qty=rcv_qty from inserted
end

DECLARE plan_cursor CURSOR FOR
SELECT ITEM_ID,plan_qty,plan_qty_h
FROM I_MKS_ITEM_PLAN
WHERE plan_qty<>0
and item_no=@item_no
order by plan_time

open plan_cursor
FETCH NEXT FROM plan_cursor
INTO @id, @plan_qty, @plan_qty_h
WHILE (@@FETCH_STATUS = 0 AND @rcv_qty>0)
begin
if @rcv_qty>=@plan_qty
begin
update I_MKS_ITEM_PLAN set plan_qty=0 where ITEM_ID=@id
set @rcv_qty=@rcv_qty-@plan_qty
end
else
begin
update I_MKS_ITEM_PLAN set plan_qty=plan_qty-@rcv_qty where ITEM_ID=@id
set @rcv_qty=0
end
FETCH NEXT FROM plan_cursor
INTO @id, @plan_qty, @plan_qty_h
end

close plan_cursor
DEALLOCATE plan_cursor