Home » SQL & PL/SQL » SQL & PL/SQL » Avoid Mutating Trigger error (9i)
Avoid Mutating Trigger error [message #349158] Fri, 19 September 2008 02:39 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

I am not yet understand what is Mutating Trigger and How to avoid this. I got the point for Mutating trigeer but I didn't get how to avoif this . So Please let me know avoiding the Mutating Truigeer.

As per my knowledge " A trigger giving a SELECT statement on a table on which trigger is trigger is written ( this is Mutating Tigeer)...

Thank you.
Re: Avoid Mutating Trigger error [message #349160 is a reply to message #349158] Fri, 19 September 2008 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know avoiding the Mutating Truigeer.

Don't use s... things in it. This error is there to tell you you are trying to do something that is not correct.

Read: Tom Kytes's The Trouble with Triggers

Regards
Michel

Re: Avoid Mutating Trigger error [message #349163 is a reply to message #349158] Fri, 19 September 2008 02:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any trigger on a table that attempts to select or modify the data in the same table, which is currently being modified runns the risk of a mutating table error.

they don't always happen, because if you execute a piece of DML which can only perform a single action (such as an INSERT INTO..VALUES...) then Oracle is smart enough to know that there is no possibility of a problem.:
SQL> create table test_0102 (col_1  number, col_2 number);

Table created.

SQL> 
SQL> create or replace trigger trg_test_0102_bri before insert on test_0102 for each row
  2  begin
  3    select count(*) into :new.col_2 from test_0102 where col_1 = :new.col_1;
  4  end;
  5  /

Trigger created.

SQL> 
SQL> insert into test_0102 (select 1,null from dual);
insert into test_0102 (select 1,null from dual)
            *
ERROR at line 1:
ORA-04091: table DEV.TEST_0102 is mutating, trigger/function may not see it
ORA-06512: at "DEV.TRG_TEST_0102_BRI", line 2
ORA-04088: error during execution of trigger 'DEV.TRG_TEST_0102_BRI'


SQL> 
SQL> insert into test_0102 values (1,null);

1 row created.
Re: Avoid Mutating Trigger error [message #349167 is a reply to message #349158] Fri, 19 September 2008 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best way to avoid mutating table errors is not to put complex application logic into triggers.
It's better to have an API interface to your tables,and put all the logic there.
Re: Avoid Mutating Trigger error [message #349187 is a reply to message #349167] Fri, 19 September 2008 04:05 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thank you very much... Now I got brief idea on this ...
Previous Topic: sql in sql
Next Topic: Help requried in Writing query with Analytical function
Goto Forum:
  


Current Time: Fri Dec 02 14:12:07 CST 2016

Total time taken to generate the page: 0.08798 seconds