Home » SQL & PL/SQL » SQL & PL/SQL » ORA-4091- Mutating Trigger Error
ORA-4091- Mutating Trigger Error [message #607435] Thu, 06 February 2014 09:51 Go to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
   
  CREATE OR REPLACE TRIGGER boin_af_rw_all_1
  AFTER INSERT OR UPDATE ON book_inventory

  FOR EACH ROW
DECLARE

  CURSOR c_prod IS
    SELECT prod.prds_product_code, prod.item_number
      FROM products prod
     WHERE :NEW.prod_prds_product_code = prod.prds_product_code
       AND (:NEW.prod_item_number = prod.item_number);

  CURSOR c_boin is
    SELECT boin.quantity_on_hand, boin.reorder_point
    
      FROM book_inventory boin, products prod
     WHERE :NEW.prod_prds_product_code = prod.prds_product_code
       AND :NEW.prod_item_number = prod.item_number
       AND boin.Prod_Prds_Product_Code = prod.prds_product_code
       AND boin.prod_item_number = prod.item_number;

BEGIN
  OPEN c_prod;
  FETCH c_prod
    INTO v_prod_code, v_item_no; /*, v_qty_onhand, v_reorder_pnt*/
  CLOSE c_prod;

  OPEN c_boin;
  FETCH c_boin
    INTO v_qty_onhand, v_reorder_pnt;
  CLOSE c_boin;

  IF v_qty_onhand < v_reorder_pnt THEN
  
    v_message_text := PKG_CCH_GLOBAL.FCT_RET_MESSAGE('05000');
    INSERT INTO test67443
      (description, col1, col2, col3, col4, col5, col6)
    values
      ('2',
       v_prod_code,
       v_item_no,
       v_qty_onhand,
       v_reorder_pnt,
       v_message_text,
       null);
  END IF;
END;


Dear Experts
Please find the Trigger Code above. When i try to insert a record into the "Book_Inventory" Table, i get the error ORA-04091 - Mutating Trigger Error. I know the reason that i am fetching a value from the table(refer cursor c_boin) on which the trigger is built. However don't know how to fix it, because i need those 2 values only from this table. Can you please help me as to how i can modify this trigger.

Thanks
Re: ORA-4091- Mutating Trigger Error [message #607437 is a reply to message #607435] Thu, 06 February 2014 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain what the trigger intends to do.

Quote:
because i need those 2 values only from this table.


Explain this too.

Re: ORA-4091- Mutating Trigger Error [message #607438 is a reply to message #607437] Thu, 06 February 2014 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E16655_01/appdev.121/e17622/triggers.htm#LNPLS99955
Re: ORA-4091- Mutating Trigger Error [message #607439 is a reply to message #607437] Thu, 06 February 2014 10:30 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
Hi
The trigger eventually will have to create a text file with some details (like the variables v_qty_onhand, v_reorder_pnt etc) if the v_qty_onhand < v_reord_pnt. This will happen either while inserting or updating the book_inventory table. The problem here is the 2 variables (v_qty_onhand, v_reorder_pnt etc) is available only on this table. Hence i will have to fetch the value from this table and do the comparison.

Hope i have answered your question? Thanks for taking your time in assisting me.

Regards
Re: ORA-4091- Mutating Trigger Error [message #607440 is a reply to message #607439] Thu, 06 February 2014 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
is available only on this table.


And there are from another row than the one that is inserted?

Re: ORA-4091- Mutating Trigger Error [message #607441 is a reply to message #607440] Thu, 06 February 2014 11:15 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
The fields (qty_on_hand & reorder_point) are from book_inventory table. So when an new records in Inserted then these 2 fields will also be inserted. But for update I intend the trigger to be fired only if either of the 2 fields are updated.

Re: ORA-4091- Mutating Trigger Error [message #607442 is a reply to message #607441] Thu, 06 February 2014 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean updated?
Are they already in the table BEFORE the INSERT or not?
Or are they part of the row you insert?

Re: ORA-4091- Mutating Trigger Error [message #607443 is a reply to message #607442] Thu, 06 February 2014 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
Since we don't have your tables or data, we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: ORA-4091- Mutating Trigger Error [message #607444 is a reply to message #607442] Thu, 06 February 2014 11:23 Go to previous messageGo to next message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
Michel Cadot wrote on Thu, 06 February 2014 11:19

What do you mean updated?
--For a Product (that already exists), i can update these 2 fields.
Are they already in the table BEFORE the INSERT or not?
-- No. Before the insert the 2 fields will not be there
Or are they part of the row you insert?
-- Yes. They will be


Re: ORA-4091- Mutating Trigger Error [message #607445 is a reply to message #607444] Thu, 06 February 2014 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Are they already in the table BEFORE the INSERT or not?
-- No. Before the insert the 2 fields will not be there


So how can you select them?

Re: ORA-4091- Mutating Trigger Error [message #607446 is a reply to message #607445] Thu, 06 February 2014 11:36 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
More to the point, if they're part of the row that caused the trigger to fire, why are you trying to select them at all?
You can reference them directly just like prod_prds_product_code and prod_item_number.
Re: ORA-4091- Mutating Trigger Error [message #607656 is a reply to message #607435] Mon, 10 February 2014 12:12 Go to previous message
praveenramaswamy
Messages: 34
Registered: December 2013
Member
Dear Experts
Thanks for taking your time and sorry for unable to respond earlier. I have fixed my problem(may be i didnt make myself clear). What i did was crated a before insert trigger on the table and i insert into a temp table.

Then i created an afterinsert trigger and i use this temp table in my cursor query. I didnt get the error and was able to accomplish what i wanted to do.

Thanks once again for your time and inputs.

Cheers
Praveen
Previous Topic: Looping in a For loop
Next Topic: Help Writing a Complex 'SELECT' Query
Goto Forum:
  


Current Time: Sat Nov 22 15:29:26 CST 2014

Total time taken to generate the page: 0.08532 seconds