Error: maximum number of recursive SQL levels (50) exceeded

Question

I want to enforce integrity so that a Person can't loan a book more than once a day. The tables & trigger compiles without errors but and i get the above error when u try to insert. I can't fix it. Syntax:

create or replace trigger chk_DateL
for insert or update on lending
COMPOUND TRIGGER
--declare
L_Date number(1);
avail varchar2(10);
subtype copy_booksRec is lending%ROWTYPE;
type copied_bks is table of copy_booksRec;
cbks copied_bks := copied_bks();

after each row is 
begin
  cbks.extend;
  cbks(cbks.last).cb_num := :new.cb_num;
  cbks(cbks.last).sb_num := :new.sb_num;
end after each row;

after statement is
begin
  for i in cbks.first .. cbks.last loop
    select loancode into avail from copy_books where num = cbks(i).cb_num;
    select count(date_L) into L_Date from lending where sb_num = cbks(i).sb_num and date_L = cbks(i).date_L;
      if (L_Date = 0) then
        insert into Lending values (cbks(i).cb_num, cbks(i).sb_num, cbks(i).date_L);
        update copy_books set loancode = 'Not' where num = cbks(i).cb_num;
--        cbks(i).date_L := cbks(i).date_L;
    else
      dbms_output.put_line('You can only make ONE LOAN in a day! You have already loaned a book on ' || L_Date);
      cbks.delete;
    end if;
  end loop;
  end after statement;
end chk_DateL;
/
show errors

Why don't you just add a unique index on Lending(cb_num, sb_num, date_L)? — Gordon Linoff
– Gordon Linoff, Commented Dec 29, 2013 at 15:26

Guntram Blohm · Accepted Answer · 2013-12-29 17:07:41Z

2

Your loop

FORALL i IN cbks.first .. cbks.last
    insert into lending values cbks(i);

inserts into the lending table, which causes the trigger to be executed again. So the trigger stacks upon itself until 50 levels are reached, then oracle stops this by throwing the error.

answered Dec 29, 2013 at 17:07

Guntram Blohm

9,8492 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ralph Blackk Over a year ago

i removed the insert stmt from this position and replaced it with the date_L allocation in the if stmt above but it still raises the same error code upon insertion. The new syntax is above now. Ps, i need a solution other than placing a unique index. Tnx

Guntram Blohm Over a year ago

That won't change anything; you still call insert into lending from within a trigger on the lending table.

Ralph Blackk Over a year ago

i dont understand the reason here. I have a couple of other triggers i created this way but they all work fine. Just like this, they are triggers created on table1, which also insert into the same table1. They use the FORALL…stmt & still work fine in 11g. Plz, what do i need to change here?

Ralph Blackk Over a year ago

Oh, i understand the problem now. Tnx for ur suggestion. ^^

Collectives™ on Stack Overflow

Error: maximum number of recursive SQL levels (50) exceeded

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related