Home » SQL & PL/SQL » SQL & PL/SQL » Mutating trigger (oracle 10g)
Mutating trigger [message #404725] Sat, 23 May 2009 01:10 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
CREATE OR REPLACE TRIGGER T8
BEFORE INSERT ON TEST1
FOR EACH ROW
BEGIN
DELETE FROM TEST1 WHERE CNO=20;
END;
/

TRIGGER CREATED

SQL> INSERT INTO TEST1 VALUES(5,60);

1 row created.

In this scenario oracle server won't raise mutating trigger error why
Re: Mutating trigger [message #404739 is a reply to message #404725] Sat, 23 May 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I truly believe you have to think about changing your profession you surely have some skills in something but obviously not here.

Regards
Michel
Re: Mutating trigger [message #404790 is a reply to message #404739] Sat, 23 May 2009 18:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think the "mutating error" you refer to is

ORA-04091: table XXXX is mutating, trigger/function may not see it.

In your example, the trigger/function doesn't actually try to look at the table, the trigger/function just closes his eyes and fires a blind delete statement into the dark. Very Happy

Re: Mutating trigger [message #409407 is a reply to message #404725] Mon, 22 June 2009 08:10 Go to previous messageGo to next message
baskar.gouda@sun.com
Messages: 4
Registered: June 2009
Location: CHENNAI
Junior Member
Here the trigger fires before the row is inserted .so mutation doesn't occur .But if you change the code to (after inplace of before) ,than the mutation will occur.


Thanks-Bhaskar Gouda

[Updated on: Mon, 22 June 2009 08:11]

Report message to a moderator

Re: Mutating trigger [message #409413 is a reply to message #409407] Mon, 22 June 2009 08:33 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
baskar.gouda@sun.com wrote on Mon, 22 June 2009 14:10
Here the trigger fires before the row is inserted .so mutation doesn't occur .But if you change the code to (after inplace of before) ,than the mutation will occur.


Thanks-Bhaskar Gouda



No it won't.
You never get mutation when doing INSERT VALUES. Oracle knows in this case that only 1 record is altered so mutation is not a problem.
If the insert was a INSERT SELECT then you'd get the error regardless of whether it's a before or after trigger.
Re: Mutating trigger [message #409416 is a reply to message #409413] Mon, 22 June 2009 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing better than a good old test:
SQL> create table t (col integer);

Table created.

SQL> create or replace trigger mytrg
  2  BEFORE insert on t
  3  for each row
  4  begin
  5    delete t where col = 1;
  6  end;
  7  /

Trigger created.

SQL> insert into t values(0);

1 row created.

SQL> create or replace trigger mytrg
  2  AFTER insert on t
  3  for each row
  4  begin
  5    delete t where col = 1;
  6  end;
  7  /

Trigger created.

SQL> insert into t values(0);
insert into t values(0)
            *
ERROR at line 1:
ORA-04091: table MICHEL.T is mutating, trigger/function may not see it
ORA-06512: at "MICHEL.MYTRG", line 2
ORA-04088: error during execution of trigger 'MICHEL.MYTRG'

Regards
Michel
Re: Mutating trigger [message #409420 is a reply to message #409416] Mon, 22 June 2009 08:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thus demonstrating the value of test cases - I thought like @Cookiemonster did.

The statement about Insert...values vs Insert...Select is partially true:
SQL> create table t (col integer);

Table created.

SQL> 
SQL> create or replace trigger mytrg
  2  BEFORE insert on t
  3  for each row
  4  begin
  5    delete t where col = 1;
  6  end;
  7  /

Trigger created.

SQL> 
SQL> insert into t select 0 from dual;
insert into t select 0 from dual
            *
ERROR at line 1:
ORA-04091: table DEV.T is mutating, trigger/function may not see it
ORA-06512: at "DEV.MYTRG", line 2
ORA-04088: error during execution of trigger 'DEV.MYTRG'
Re: Mutating trigger [message #409421 is a reply to message #404725] Mon, 22 June 2009 09:04 Go to previous message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
I stand corrected.
I thought oracle did that optimisation regardless of before/after.

Guess I've never checked because I'd never code a trigger like that regardless. I've got a few like that other people have written and I just end up wishing they hadn't so I can do insert/select to speed up processing.
Previous Topic: Problem in Trigger when it is updating
Next Topic: missing expression error with update subquery
Goto Forum:
  


Current Time: Tue Dec 06 14:09:04 CST 2016

Total time taken to generate the page: 0.15555 seconds