Home » SQL & PL/SQL » SQL & PL/SQL » mutating trigger (merged)
mutating trigger (merged) [message #226820] Mon, 26 March 2007 12:55 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I created a trigger on EMP

Create Or Replace Trigger Trig
Before Insert Or Update of Sal Or Delete
On EMP
Referencing old As retold
new As ritnew
For Each Row
Declare
cnt Number;
vsal Number;
Begin
Select Count(*),:ritnew.sal+ Sum(Sal) Into cnt,vsal
From EMP;
DBMS_OUTPUT.PUT_LINE('Count = '||cnt);
DBMS_OUTPUT.PUT_LINE('Total sal = '||vsal);
End;

SQL> Begin
Update EMP
Set SAL = 435
Where EMPNO = 7782;
End;
/
Begin
*
ERROR at line 1:
ORA-04091: table RIT.EMP is mutating, trigger/function may not see it
ORA-06512: at "RIT.TRIG", line 5
ORA-04088: error during execution of trigger 'RIT.TRIG'
ORA-06512: at line 2

SQL> Delete from emp where empno = 1004;
delete from emp where empno = 1004
*
ERROR at line 1:
ORA-04091: table RIT.EMP is mutating, trigger/function may not see it
ORA-06512: at "RIT.TRIG", line 4
ORA-04088: error during execution of trigger 'RIT.TRIG'


SQL>Begin --Successful
Insert Into EMP (EMPNO,DEPTNO,SAL) Values(2001,10,289);
End;
/
Count = 24
Total sal = 129313

But u know really the count is 25, but why it is not throwing mutating table error
please guide me

Re: mutating trigger [message #226836 is a reply to message #226820] Mon, 26 March 2007 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you should Google or search on AskTom for "mutating error".
I think you should not use trigger for what they are not made.

Regards
Michel
Re: mutating trigger [message #226849 is a reply to message #226836] Mon, 26 March 2007 15:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Additionally, DBMS_OUTPUT serves no purpose in a trigger as you cannot see output from a trigger.
mutating [message #231191 is a reply to message #226820] Sun, 15 April 2007 21:37 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

How can u bypass a mutating table error using Temporary table,PL/SQL table?
Mr. moderator i tried but can't get it,so please help
Re: mutating [message #231194 is a reply to message #231191] Sun, 15 April 2007 22:02 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Can you just explain briefly, what exactly you want?

Regards,
Harshad
Re: mutating [message #231218 is a reply to message #231191] Mon, 16 April 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sometimes you can.
Sometimes you can't.

Regards
Michel
Re: mutating [message #231243 is a reply to message #231218] Mon, 16 April 2007 01:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And none of the threads you found here on the OraFAQ forums could help you out?

MHE
Re: mutating trigger [message #231315 is a reply to message #226820] Mon, 16 April 2007 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
dupe.
Re: mutating [message #231346 is a reply to message #231194] Mon, 16 April 2007 12:11 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

i was asking what is the solution to it(Mutating Trigger error)?
Re: mutating trigger (merged) [message #231347 is a reply to message #226820] Mon, 16 April 2007 12:18 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>i was asking what is the solution to it(Mutating Trigger error)?
The solution is within the trigger code do NOT do DML against the table upon which the trigger is based.
Re: mutating trigger (merged) [message #231433 is a reply to message #231347] Tue, 17 April 2007 01:28 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Back to your original question. Being not fully awake yet, I can't guarantee that the following is entirely correct.

You have:
1. Table X
2. A BEFORE insert/update/delete STATEMENT trigger

A. You perform an UPDATE on X: mutating table error.
The trigger is trying to read records that are being accessed by your UPDATE statement.

B. You perform a DELETE on X: mutation table error.
The trigger is trying to read records that are being accessed by your DELETE statement.

C. You perform an INSERT on X: no error.
The trigger reads no records of your INSERT statement because it is a BEFORE trigger, the actual insert has not taken place yet. This also explains the count.

Follow the general advice already mentioned here: avoid such constructions when possible. And DBMS_OUTPUT in triggers isn't a good idea either.

MHE

[Updated on: Tue, 17 April 2007 01:28]

Report message to a moderator

Previous Topic: Oracle Driver failing to insert values to CLOB
Next Topic: Upload .PDF to a BLOB field
Goto Forum:
  


Current Time: Mon Dec 05 06:48:08 CST 2016

Total time taken to generate the page: 0.04962 seconds