Re: trigger firing three times ?????

Are you sure your update doesn't affect 3 rows? Your trigger fires once for each row updated.

DK

<parenthetical_at_my-deja.com> wrote in message news:8vf563$i0l$1_at_nnrp1.deja.com...
> 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 Thu Nov 23 2000 - 18:19:39 CST