Home » SQL & PL/SQL » SQL & PL/SQL » How to create an autonomous tranaction to solve the mutation error (oracle 8i)
How to create an autonomous tranaction to solve the mutation error [message #318185] Tue, 06 May 2008 01:09 Go to next message
aneeshrkurup
Messages: 2
Registered: May 2008
Junior Member
Hai friends I created a trigger but it is showing mutation error ,I want to overcome this mutation error,so some of my friends advice me to use autonomous transaction


create or replace trigger upd_location after
insert on emp for each row
declare
l_emp_count number:= 6;
begin



The following statement(with in comments) creating a mutation error.(try to insert and retrive on the same table causing this issue)

To solve the problem,I want to use a package insted of the following code,but i don't know the procedure to create an autonomous transaction.pls help me..

/*

select count(empno)
into l_emp_count
from emp
where deptno = :new.deptno;

*/

update dept set loc=loc || ', ' ||
to_char(l_emp_count)
where deptno = :new.deptno;
end;
/

Re: How to create an autonomous tranaction to solve the mutation error [message #318186 is a reply to message #318185] Tue, 06 May 2008 01:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Moved to newbies forum.
Read the sticky in the expert forum to see why.

Why would you update your parent-table with a count? This is bound to go wrong..

[Updated on: Tue, 06 May 2008 01:15]

Report message to a moderator

Re: How to create an autonomous tranaction to solve the mutation error [message #318187 is a reply to message #318185] Tue, 06 May 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so some of my friends advice me to use autonomous transaction

They are not your friend, they just want you to lose your job.
NEVER do that.

You can't read the table that is currently modified in a trigger.
The same question has been asked in Newbie forum 2 days ago:
Trigger problem with nested query.
Please search BEFORE posting.

Regards
Michel
Re: How to create an autonomous tranaction to solve the mutation error [message #318188 is a reply to message #318185] Tue, 06 May 2008 01:17 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I'm not quite sure if the "autonomous transaction" would be a good idea to overcome the mutating table error.

But please, have a look at what Tom Kyte writes
He's not only giving a solution, but describes it very well.

It's a solution I've implemented a few times already.
Re: How to create an autonomous tranaction to solve the mutation error [message #318192 is a reply to message #318188] Tue, 06 May 2008 01:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note the purpose is to log something.
This is the only case you can use it or autonomous transaction (depending if you want transaction or mandatory logging).

It must NOT be used in the present case. It will not work unless you lock the table.

Regards
Michel
Previous Topic: A function to replace CAPS
Next Topic: Is function return null value?
Goto Forum:
  


Current Time: Sat Dec 03 20:10:31 CST 2016

Total time taken to generate the page: 0.09259 seconds