Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04091: table Table_Name is mutating, trigger/function may not see it (Oracle 11g With Apex 5.1)
ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670567] Fri, 13 July 2018 06:19 Go to next message
Mukul Ahmed
Messages: 16
Registered: December 2015
Location: Bangladesh
Junior Member
I have written a trigger for inserting to a new table from one table.
Here is the code-

CREATE OR REPLACE TRIGGER TMS_DUE_PAY_RCV_TRIG 
AFTER INSERT ON TMS_DUE_PAY_RCV 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE 
    v_trans_type VARCHAR2(10);

BEGIN 

 select TRANS_TYPE into v_trans_type from TMS_DUE_PAY_RCV where PAYMENT_ID=:NEW.PAYMENT_ID;
     
    if
        v_trans_type = 'TP' then
        
         INSERT INTO TMS_TRANSACTION (TRANS_DATE, TRANS_TYPE, INFO_ID, CASH_IN_INFO,CASH_OUT_INFO, CASH_IN_AMT, CASH_OUT_AMT,CREATE_BY,STATUS )
                              values(:NEW.PAY_DATE,'CASH OUT', :NEW.PAYMENT_ID,NULL, 'Purchase Due Payment',0, :NEW.PAID_AMT,:NEW.CREATE_BY,'A');    
                                     
    else

        INSERT INTO TMS_TRANSACTION (TRANS_DATE, TRANS_TYPE, INFO_ID, CASH_IN_INFO, CASH_OUT_INFO, CASH_IN_AMT, CASH_OUT_AMT,CREATE_BY, STATUS )
                    values(:NEW.PAY_DATE,'CASH IN', :NEW.PAYMENT_ID, 'Due Received',NULL, :NEW.PAID_AMT,0, :NEW.CREATE_BY,'A');    
       
    end if;
                                                                    
END TMS_DUE_PAY_RCV_TRIG ;
/

But when inserting then an error is showing like-

'ORA-04091: table TMS_DUE_PAY_RCV is mutating, trigger/function may not see it ORA-06512: at "TMS_DUE_PAY_RCV_TRIG",
 line 7 ORA-04088: error during execution of trigger 'TMS.TMS_DUE_PAY_RCV_TRIG

Please Help me..

-----
Lalit: Formatted code and error block

Code is different from English, hence must be treated differently. In future, please do by yourself as suggested in below message.

[Updated on: Sat, 21 July 2018 11:58] by Moderator

Report message to a moderator

Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670568 is a reply to message #670567] Fri, 13 July 2018 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Why didn't you feedback and thank people in your previous topics?

And as BlackSwan said in many of them (adding a link to Google which I won't do as you should know how to use it now):

Quote:
unwilling or incapable to use GOOGLE yourself?

[Updated on: Fri, 13 July 2018 06:35]

Report message to a moderator

Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670569 is a reply to message #670568] Fri, 13 July 2018 06:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't query the table the trigger is based on.
Assuming TMS_DUE_PAY_RCV.PAYMENT_ID is unique you can replace this:
select TRANS_TYPE into v_trans_type from TMS_DUE_PAY_RCV where PAYMENT_ID=:NEW.PAYMENT_ID;

if
v_trans_type = 'TP' then
with:
IF :new.trans_type = 'TP' then
Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670570 is a reply to message #670568] Fri, 13 July 2018 07:51 Go to previous messageGo to next message
Mukul Ahmed
Messages: 16
Registered: December 2015
Location: Bangladesh
Junior Member
Hi Michel Cadot,

Sorry for late to reply. Many thanks for being with me. In future i mus will follow your advice.
Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670571 is a reply to message #670569] Fri, 13 July 2018 07:57 Go to previous messageGo to next message
Mukul Ahmed
Messages: 16
Registered: December 2015
Location: Bangladesh
Junior Member
Hi cookiemonster,

Many many thanks for helping me..
I was thinking about it.

it worked..!!!

Thanks again.

Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670572 is a reply to message #670570] Fri, 13 July 2018 08:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've got to wonder why you are trying to use the select at all since you know about :new values - they're in the insert already.
Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670573 is a reply to message #670572] Fri, 13 July 2018 08:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Personally I'd rewrite that so that it uses variables for the values that differ based on trans_type, set them in an IF and then do a single insert statement at the end.
Re: ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670695 is a reply to message #670567] Fri, 20 July 2018 04:16 Go to previous message
elmomagalona
Messages: 1
Registered: March 2018
Junior Member
Thank u Mukul for your valuable information about how inserting possible to a new table from one table.
Previous Topic: Get last record from another table
Next Topic: Oracle materialized views workflow
Goto Forum:
  


Current Time: Thu Mar 28 04:56:43 CDT 2024