CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Murshed
Murshed

Reputation: 3

maximum number of recursive SQL levels (50) exceeded

I would like to create primary key without using sequence but I don't mind using trigger. I am using oracle 11gR2. my script contains the following. a. create table (dept2) b. insert sample data c. create trigger (find max value, add 1, insert) d. insert another record (trigger should fire) it gives the above error. below is my script.

drop table dept2 ;
create table dept2
(
deptno number(2),
dname varchar2(20),
mgr number(3)
) ;

insert into dept2 values (11, 'a', 23) ;
insert into dept2 values (12, 'b', 24) ;

select * from dept2 ;

CREATE OR REPLACE TRIGGER trig_deptno2
BEFORE INSERT ON dept2
FOR EACH ROW
DECLARE v_deptno NUMBER(2) ;
BEGIN
    SELECT MAX(deptno) INTO v_deptno
    FROM dept2 ;
    INSERT INTO dept2 VALUES
    ((v_deptno + 1), :NEW.dname, :NEW.mgr) ;
END trig_deptno2 ;
/
SHOW ERR
L

insert into dept2 (dname, mgr) values ('d', 24) ;
select * from dept2 ;
L

Can anyone tell me where is the mistake or how I can correct the error?

Upvotes: 1

Views: 11550

Answers (3)

Mureinik
Mureinik

Reputation: 311796

Your trigger fires when inserting to dept2, and then performs an insert on dept2, which causes the trigger to re-fire, etc. in an endless recursion.

You should not call insert explicitly - just update the :NEW record, and once the trigger is completed, that updated record will be inserted into the table:

CREATE OR REPLACE TRIGGER trig_deptno2
BEFORE INSERT ON dept2
FOR EACH ROW
DECLARE v_deptno NUMBER(2) ;
BEGIN
    SELECT MAX(deptno) + 1 INTO :NEW.deptno
    FROM   dept2;
END trig_deptno2 ;
/

Upvotes: 2

Thilo
Thilo

Reputation: 262684

Why do you want to do that?

If you need auto-generated sequence numbers, use a sequence.

Two main problems with SELECT MAX(id) + 1:

  1. It does not work with concurrent transactions

  2. It repeats numbers when you delete the current "max"

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270361

You are doing a "before insert" trigger. In such a trigger, you don't insert into the same table -- otherwise, you can get an infinite loop. Just assign to the variable value:

CREATE OR REPLACE TRIGGER trig_deptno2
BEFORE INSERT ON dept2
FOR EACH ROW
DECLARE v_deptno NUMBER(2) ;
BEGIN
    SELECT coalesce(MAX(deptno) + 1, 1) INTO :new.deptno
    FROM dept2 ;
END trig_deptno2 ;

When the trigger is finished, it will insert the right values from the new variable.

Let me just say that sequences exist for a reason, and you should use them for this purpose. Trying to mimic their functionality is likely to lead to errors.

Upvotes: 4

Related Questions