PLSQL-在触发器中插入Iinsert导致递归循环 [英] 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.

我的触发器

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;

但是我得到了错误

从命令第2行开始的错误-INSERT INTO KEYWORD VALUES(75，'SDFSDF'，1000)错误报告-SQL错误:ORA-00036:最大值 递归SQL级别(50)的数量超过ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第4行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行以下错误 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行以下错误 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行ORA-04088:执行过程中出错 触发"SW3.TRG_INSERTKEYWORDS" ORA-06512: "SW3.TRG_INSERTKEYWORDS"，第8行 00036.00000-超出了递归SQL级别的最大数量(％s)" *原因:尝试超过指定的数量 递归SQL级别. *操作:删除递归SQL，可能是递归触发器.

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.

推荐答案

您当前的逻辑会不断触发其中的每个INSERT触发器！

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

因此，也许您使用VIEW寻找INSTEAD OF触发器

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 !

查看:

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

触发器:

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导致递归循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！