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 Maheswaran Ravisankar · Accepted Answer · 2014-11-10T20:37:42

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;

PLSQL - Iinsert in trigger causing recursive loop

2 Answers