Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Mutating Table error
Vidhu
post Dec 28 2016, 05:00 AM
Post #1


Advanced Member
***

Group: Members
Posts: 47
Joined: 11-March 15
From: India
Member No.: 50,402



Hi Team,

I was reading the below link on mutating table error.
http://www.dba-oracle.com/t_avoiding_mutat...table_error.htm

It's mentioned that:

Use an "after" or "instead of" trigger - If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.

To check this scenario i created a before insert trigger and an after insert trigger as given below
--------------------------------------------------------------------------------------------------------------------
create or replace trigger trg_bef_insert_emp
before insert on employees
for each row

declare
counter number := 0;
begin

select count(emp_name) into counter
from employees where emp_id = 100;

insert into employees_log values(:new.emp_id, 'Before Insert',NULL,:new.emp_id);
insert into employees_log values(:new.emp_id, 'Before Insert',NULL,:new.emp_name);

end;
---------------------------------------------------------------------------------------------------------------
create or replace trigger trg_aft_insert_emp
after insert on employees
for each row

declare
counter number := 0;
begin

select count(emp_name) into counter
from employees where emp_id = 100;
insert into employees_log values(:new.emp_id, 'After Insert',NULL,:new.emp_id);
insert into employees_log values(:new.emp_id, 'After Insert',NULL,:new.emp_name);

end;
----------------------------------------------------------------------------------------------------------------

Now when i tried to insert a record in to employees table, then i got mutating table error due to the trigger trg_aft_insert_emp. However the trigger trg_bef_insert_emp doesn't give mutating table error. Please see error details below:

ORA-04088: error during execution of trigger 'SYSADMIN.TRG_AFT_INSERT_EMP'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.

But based on the link I have shared above it says we need to use after trigger to avoid mutating table error. Seems to be confusing now :-)

Can somebody explain me the reason. Or please correct me if am in the wrong direction.


--------------------
Regards,

Vidhu V S
Oracle PL SQL Developer
Go to the top of the page
 
+Quote Post
burleson
post Dec 28 2016, 12:05 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,438
Joined: 26-January 04
Member No.: 13



Hi Vidhu,,

As noted, the best approach is not to use triggers!

>> the trigger trg_bef_insert_emp doesn't give mutating table error. Please see error details below:

Then I would stay with a "before" trigger.




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Jan 3 2017, 05:15 PM
Post #3


Advanced Member
***

Group: Members
Posts: 61
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Vidhu,

The mutating table error arises only when you reference the table that has been used in the trigger's DML clause (INSERT ON, UPDATE ON, DELETE ON clauses). You will get this error even if you use a BEFORE or AFTER trigger.

As Mr. Burleson suggested, you can very well find a suitable solution other than using Triggers.


But if you WANT to use the triggers and avoid mutating table error, you can very well the PRAGMA AUTONOMOUS_TRANSACTION in the trigger body and it wont give you any error. But, the results you get may be wrong due to the incomplete transactions in that table.

You can also use COMPOUND triggers to solve this problem.

Thank you,
Boobal Ganesan

Go to the top of the page
 
+Quote Post
boobal_ganesan
post Jan 11 2017, 06:34 AM
Post #4


Advanced Member
***

Group: Members
Posts: 61
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Vidhu,

The mutating table error arises only when you reference the table that has been used in the trigger's DML clause (INSERT ON, UPDATE ON, DELETE ON clauses). You will get this error even if you use a BEFORE or AFTER trigger.

As Mr. Burleson suggested, you can very well find a suitable solution other than using Triggers.


But if you WANT to use the triggers and avoid mutating table error, you can very well the PRAGMA AUTONOMOUS_TRANSACTION in the trigger body and it wont give you any error. But, the results you get may be wrong due to the incomplete transactions in that table.

You can also use COMPOUND triggers to solve this problem.

Thank you,
Boobal Ganesan
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 27th June 2017 - 05:26 AM