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..
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: 65562
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: 13168
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: 13168
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 message
cookiemonster
Messages: 13168
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.
Previous Topic: Need Help on Creating FUNCTION
Next Topic: Generate New Excel format (XLSX) files using Oracle PLSQL
Goto Forum:
  


Current Time: Wed Jul 18 00:18:18 CDT 2018