CODEWITHSUNDEEP
oracle-databaseplsqlinserttriggers
Alan Mulligan
Alan Mulligan

Reputation: 1198

PLSQL - Iinsert in trigger causing recursive loop

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.

Upvotes: 1

Views: 1463

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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;

Upvotes: 2

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

Reputation: 50017

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.

Upvotes: 1

Related Questions