Home » SQL & PL/SQL » SQL & PL/SQL » Getting Mutating error in Trigger
Getting Mutating error in Trigger [message #333354] Fri, 11 July 2008 06:28 Go to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
I created FOR EACH ROW Trigger
SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger emp1_tgr
  2  after delete on emp1 for each row
  3  declare n integer;
  4  begin
  5  select count(*) into n from emp1;
  6  dbms_output.put_line('there are'||n||'rows in employees');
  7* end;
SQL> /
Trigger created.

when i delete the record from emp1 getting the following error

SQL> delete from emp1;
delete from emp1
            *
ERROR at line 1:
ORA-04091: table SCOTT.EMP1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.EMP1_TGR", line 3
ORA-04088: error during execution of trigger 'SCOTT.EMP1_TGR'


can anyone tell me why i am getting the error?

[EDITED by LF: fixed subject typo; was "Mutuating"]

[Updated on: Fri, 11 July 2008 10:57] by Moderator

Report message to a moderator

Re: Getting Mutuating error in Trigger [message #333359 is a reply to message #333354] Fri, 11 July 2008 06:51 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
That's because you are accessing the table emp within the trigger that's defined on the table.

Do a google-search on "mutating table error" and you'll get a bunch of hits explaining all this and in more detail
Re: Getting Mutuating error in Trigger [message #333646 is a reply to message #333359] Mon, 14 July 2008 01:04 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
If i use Statement Level Trigger i am not getting the Mutuating Error. Any one please tell me the Reason
Re: Getting Mutuating error in Trigger [message #333651 is a reply to message #333646] Mon, 14 July 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because there is no more possible error as the statement is over (or is not already started).
Or in short, because this is how it works.

Regards
Michel

[Updated on: Mon, 14 July 2008 01:26]

Report message to a moderator

Re: Getting Mutuating error in Trigger [message #333652 is a reply to message #333646] Mon, 14 July 2008 01:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you search the web as MarcS advised you to?
What did you find? Why did that not explain what you are asking?
Previous Topic: How to write the query?
Next Topic: Function Base Index with “INITIALLY DEFERRED” option
Goto Forum:
  


Current Time: Sat Dec 10 01:21:38 CST 2016

Total time taken to generate the page: 0.09523 seconds