Home » SQL & PL/SQL » SQL & PL/SQL » hi, why it is giving error when inserting row on a table which had triggers on it
hi, why it is giving error when inserting row on a table which had triggers on it [message #231412] Mon, 16 April 2007 23:54 Go to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

hi,
in my emp table,
there are three triggers,
1) before delete on emp
2)after delete on emp
3)before insert on emp

1) and 2) are working perfectly,
for the third one it is giving the below error why,

insert into emp1 values(3333,'raghu','manager','','27-jan-1982',5000,500,40);
2 *
3 ERROR at line 1:
4 ORA-00036: maximum number of recursive SQL levels (50) exceeded
5 ORA-00036: maximum number of recursive SQL levels (50) exceeded
6 ORA-00036: maximum number of recursive SQL levels (50) exceeded
7 ORA-06512: at "SCOTT.BEF_INSERT_ON_EMP1", line 2
8 ORA-04088: error during execution of trigger 'SCOTT.BEF_INSERT_ON_EMP1'
9 ORA-06512: at "SCOTT.BEF_INSERT_ON_EMP1", line 2
10 ORA-04088: error during execution of trigger 'SCOTT.BEF_INSERT_ON_EMP1'
11 ORA-06512: at "SCOTT.BEF_INSERT_ON_EMP1", line 2
12 ORA-04088: error during execution of trigger 'SCOTT.BEF_INSERT_ON_EMP1'
13 ORA-06512: at "SCOTT.BEF_INSERT_ON_EMP1", line 2
14 ORA-04088: error during execution of trigger 'SCOTT.BEF_INSERT_ON_EMP1'
15 ORA-06512: at "SCOTT.BEF_INSERT_ON_EMP1", line 2

guide me
bye
raghu
Re: hi, why it is giving error when inserting row on a table which had triggers on it [message #231413 is a reply to message #231412] Tue, 17 April 2007 00:04 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,



Quote:
Error: ORA 36
Text: Maximum number of recursive sql levels (%s) exceeded
---------------------------------------------------------------------------
Cause: An attempt was made to go more than the specified number of recursive
SQL levels was made.
Action: Remove the recursive SQL, possibly a recursive trigger.

*** Important: The notes below are for experienced users - See [NOTE:22080.1]

In Oracle7/8 there is a hard carded limit of 50 recursive
sessions. This hard coded limit is NOT configurable.

If a you have hit 50 recursive sessions there is probably something
wrong in the code causing an infinite recursion.
Eg: PLSQL which uses DBMS_SQL to call itself over and over.

The following may help collect additional information about the
true problem.
NB: See [NOTE:75713.1] before setting any event in the init.ora file.

Add this line to the init.ora file for the instance:

event="36 trace name errorstack level 3"

This should dump a trace file when the ORA-36 occurs. The trace file may
help identify the cause of the problem.


regards,
shanth
Re: hi, why it is giving error when inserting row on a table which had triggers on it [message #231419 is a reply to message #231412] Tue, 17 April 2007 00:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
iitraghu wrote on Tue, 17 April 2007 06:54
hi,
in my emp table,
there are three triggers,
1) before delete on emp
2)after delete on emp
3)before insert on emp


If you want us to help you, you should post the contents of your before insert on emp.
My guess is that it has an insert on emp in it:
SQL> drop table faq;

Table dropped.

SQL> create table faq (id number);

Table created.

SQL> create or replace trigger faq_bir
  2  before insert on faq
  3  for each row
  4  begin
  5    insert into faq (id) values (:new.id);
  6  end;
  7  /

Trigger created.

SQL> insert into faq values (1);
insert into faq values (1)
            *
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execution of trigger 'SCOTT.FAQ_BIR'
ORA-06512: at "SCOTT.FAQ_BIR", line 2
ORA-04088: error during execut

Please also tell us what the purpose of your trigger is.
icon3.gif  Re: hi, why it is giving error when inserting row on a table which had triggers on it [message #231425 is a reply to message #231419] Tue, 17 April 2007 00:47 Go to previous messageGo to next message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

my purpose of this trigger is to add the 10000 amount to sal field
before inserting..

create trigger bef_insert_on_emp2
before insert on emp2
for each row
begin
insert into emp2(empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno)
values
(:new.empno,
:new.ename,
:new.job,
:new.mgr,
:new.hiredate,
:new.sal+10000,
:new.comm,
:new.deptno);
end;
Re: hi, why it is giving error when inserting row on a table which had triggers on it [message #231426 is a reply to message #231425] Tue, 17 April 2007 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The trigger fires when you insert a record, so you should not insert another one in your trigger.
Just assign a new value to the relevant :new.column and let Oracle handle the insert.
eg:
:new.id := :new.id + 10000;

[Updated on: Tue, 17 April 2007 00:52]

Report message to a moderator

Re: hi, why it is giving error when inserting row on a table which had triggers on it [message #231439 is a reply to message #231426] Tue, 17 April 2007 02:05 Go to previous message
iitraghu
Messages: 18
Registered: April 2007
Location: bengalore
Junior Member

sorry,

this is minimum basic,

i got the diff
Previous Topic: Upload .PDF to a BLOB field
Next Topic: Oracle table to xls file
Goto Forum:
  


Current Time: Thu Dec 08 20:34:18 CST 2016

Total time taken to generate the page: 0.11016 seconds