Home » SQL & PL/SQL » SQL & PL/SQL » Mutating trigger error
Mutating trigger error [message #251527] Sat, 14 July 2007 02:52 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I created a table named PROF as

SQL> Create Table PROF(SYSID Number(3) Default 100 Not Null,
ID Number Constraint unqe Unique,
Name Varchar2(20) Check(Name Not In'Ritesh')),
JOBID Number(10),
Constraint prof_pk Primary Key(SYSID),
Foreign Key(JOBID) References PROF_JOB);
and a child table named PROF_JOB

SQL> Create Table PROF_JOB(JOBCODE Number(4) Not Null,
JOBDESC Varchar2(25) );

SQL> Alter Table PROF_JOB
Add Constraint prof_job_pk Primary Key(JOBCODE);

then I created a trigger named prof_rec

SQL> Create Or Replace Trigger prof_rec After Insert On PROF
For Each Row
Begin
Insert Into PROF Values(prof_seq.NEXTVAL,:New.ID,:New.NAME,:New.JOBID);
--Select prof_seq.NEXTVAL Into SYSID From DUAL;
End;
/
then I tried to Insert a record in PROF BUT IT GAVE AN ERROR SAYING

SQL> Insert Into PROF(ID,NAME,JOBID) Values(120,'Ramesh',120);

Insert Into PROF(ID,NAME,JOBID) Values(120,'Ramesh',120)
*
ERROR at line 1:
ORA-04091: table RIT.PROF is mutating, trigger/function may not see it
ORA-06512: at "RIT.PROF_REC", line 2
ORA-04088: error during execution of trigger 'RIT.PROF_REC'

Keeping the trigger body same i changed the timing means from After Insert i gave Before Insert and tried Insert but it gave

SQL> Create Or Replace Trigger prof_rec Before Insert On PROF
For Each Row
Begin
Insert Into PROF Values(prof_seq.NEXTVAL,:New.ID,:New.NAME,:New.JOBID);
--Select prof_seq.NEXTVAL Into SYSID From DUAL;
End;
/
SQL> Insert Into PROF(ID,NAME,JOBID) Values(120,'Ramesh',120);

ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT.PROF_REC'
ORA-06512: at "RIT.PROF_REC", line 4
ORA-04088: error during execution of trigger 'RIT

Can u please give help to understand why this trigger is behaving in this manner
Please guide me on this
Thank you
Re: Mutating trigger error [message #251529 is a reply to message #251527] Sat, 14 July 2007 03:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then, your insert calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts that calls the trigger that inserts...
Do you follow what I mean?

This is also the reason of your mutating error.

You can't update/insert/delete in the table you are currently updating/inserting/deleting.

To achieve what you want to do, your trigger must have only one line: "select prof_seq.NEXTVAL Into :new.sysid From DUAL;"

[Edit] As this is the fourth topic you start on "mutating trigger" error you post, I'm expecting you understand now.

Regards
Michel

[Updated on: Sat, 14 July 2007 03:11]

Report message to a moderator

Previous Topic: table or view does not exists
Next Topic: I am an arrogant SOB - string compare
Goto Forum:
  


Current Time: Sun Dec 04 08:49:11 CST 2016

Total time taken to generate the page: 0.05447 seconds