PLSQL - Iinsert in trigger causing recursive loop

Question

I have a trigger which runs when on an insert in to my keyword table. The key word is taken in as a string and separated by a comma. I have separated each value and then try insert them in each value into the table. But when I run the insert I get an error.

My trigger

create or replace TRIGGER trg_INSERTKEYWORDS
BEFORE INSERT ON Keyword
FOR EACH ROW
DECLARE
varKeyWordsStr VARCHAR2 (255) := 'Hello,How,are,you,keeping';
BEGIN
FOR k IN (SELECT REGEXP_SUBSTR (varKeyWordsStr,'[^,]+',1,LEVEL) keyWord
           FROM DUAL
           CONNECT BY REGEXP_SUBSTR (varKeyWordsStr,'[^,]+',1,LEVEL)IS NOT NULL)
LOOP
  INSERT INTO KEYWORD VALUES(seqKeyWord.NEXTVAL,k.keyWord,1000);
END LOOP;
END;

But i get the error

Error starting at line : 2 in command - INSERT INTO KEYWORD VALUES(75,'SDFSDF',1000) Error report - SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 4 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 ORA-04088: error during execution of trigger 'SW3.TRG_INSERTKEYWORDS' ORA-06512: at "SW3.TRG_INSERTKEYWORDS", line 8 00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded" *Cause: An attempt was made to go more than the specified number of recursive SQL levels. *Action: Remove the recursive SQL, possibly a recursive trigger.

Maheswaran Ravisankar · Accepted Answer · 2014-11-10 22:38:02Z

2

Your current logic keeps firing the trigger itself on every INSERT inside it!

So, Perhaps you look for a INSTEAD OF trigger using a VIEW

create or replace TRIGGER trg_INSERTKEYWORDS
INSTEAD OF INSERT ON Keyword

It is like, instead of the actual insert on view, do what I tell in my trigger logic !

View:

CREATE VIEW MYVIEW AS 
SELECT
  LISTAGG(keyword,',') WITHIN GROUP (ORDER BY seq) as keyword,
  some_code
 FROM  Keyword
GROUP BY some_code;

The trigger:

create or replace TRIGGER trg_INSERTKEYWORDS
INSTEAD OF INSERT ON MYVIEW 
FOR EACH ROW
DECLARE
varKeyWordsStr VARCHAR2 (255) := 'Hello,How,are,you,keeping';
/* Isn't it keyWord from the inserted value ?? */
BEGIN
  FOR k IN (SELECT REGEXP_SUBSTR (varKeyWordsStr,'[^,]+',1,LEVEL) keyWord
             FROM DUAL
            CONNECT BY REGEXP_SUBSTR (varKeyWordsStr,'[^,]+',1,LEVEL)IS NOT NULL)
  LOOP
      INSERT INTO KEYWORD VALUES(seqKeyWord.NEXTVAL,k.keyWord,1000);
  END LOOP;
END;

edited Nov 10, 2014 at 22:38

answered Nov 10, 2014 at 20:37

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

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

3 Comments

Alan Mulligan Over a year ago

Error: ORA-25002: cannot create INSTEAD OF triggers on tables. Any other ideas

Maheswaran Ravisankar Over a year ago

can't you create a view for your INSERT ?

Alan Mulligan Over a year ago

Sorry How do you mean

Bob Jarvis - Слава Україні · Accepted Answer · 2014-11-10 20:32:56Z

1

This trigger is inserting into the KEYWORD table during a trigger on an insert into KEYWORD. Each of the INSERTs in the trigger is causing the trigger to be fired again, which then tries to do all the INSERTs again, re-firing the trigger, etc, etc. Frankly I'm surprised you didn't get an ORA-04091 mutating table error, but whatever. Basically, don't insert into the table that a trigger is defined on while in the middle of the trigger.

Share and enjoy.

answered Nov 10, 2014 at 20:32

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

PLSQL - Iinsert in trigger causing recursive loop

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related