trigger firing three times ?????

I am having trouble with a trigger I've written for an oracle class. The trigger should update a reorder table when an inventory amount falls below a minimum number. The update reorder procedure checks if the item is already in the reorder table, and if so, adds additional stock to reorder -- and if not, inserts the new item number with a new reorder amount.

The trouble I'm having is the update statement works fine, but the insert statement gives me mutating table error. Why does the update squl work and the insert sql not?

Also, the trigger seems to be firing three times. I'm getting my output messages three times, and the update sql is adding the amount X3.

The code is posted below if anyone could see where I've gone wrong.

Many thanks.

• TRIGGER CODE create or replace trigger Update_Reorders_313884 after insert or update on inventory_313884 for each row

DECLARE
BEGIN
IF :new.Quantity_On_Hand < :new.Quantity_Minimum THEN   Update_Reorder_Table_313884(:new.Item_Number, :new.Quantity_On_Hand, :new.Quantity_Minimum);

ELSE
  NULL; END IF;
END;
/

• PROCEDURE CODE FOR REORDER TABLE CREATE OR REPLACE PROCEDURE Update_Reorder_Table_313884 ( arg_Item_Number varchar2, arg_Current_Quantity number, arg_Minimum_Quantity number ) IS

Current_Item_Number		Reorder_313884.To_Reorder%type;
Current_Reorder_Amount		Reorder_313884.To_Reorder%type;
Overstock			number := 100;

Cursor Cur_Record is
  SELECT Item_number, To_Reorder
  FROM Reorder_313884
  WHERE Item_number = arg_Item_Number;

BEGIN Open Cur_Record;

/* Check if Item Number is already included in Reorder Table */

Fetch Cur_Record into
Current_Item_Number, Current_Reorder_Amount;

IF Cur_Record%notfound THEN

  Current_Reorder_Amount := (arg_Minimum_Quantity - arg_Current_Quantity) + Overstock;
  Current_Item_Number := arg_Item_Number;

  INSERT INTO Reorder_313884
    (Item_Number, To_Reorder)
  VALUES
    (Current_Item_Number, Current_Reorder_Amount);

ELSE   Current_Reorder_Amount := (Current_Reorder_Amount + Overstock);   Current_Item_Number := arg_Item_Number;

  UPDATE Reorder_313884
  SET To_Reorder = Current_Reorder_Amount   WHERE Item_Number = Current_Item_Number;

END IF;   Close Cur_Record;

END;
/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 21 2000 - 18:49:09 CST