代码之家 › 专栏 › 技术社区 › Hayra

Oracle upsert触发器错误“ORA-00036:超过递归SQL级别的最大数目(50)”

upsert triggers plsql oracle

Hayra · 技术社区 · 7 年前

我一直在研究一个简单的upsert触发器,它使用与一个名为vibki的列相关的唯一索引。

便于访问的表DLL信息:

  CREATE TABLE "D2C_EVENT_GENERATION_BOM" 
   (    "VIBKI" VARCHAR2(40 BYTE), 
    "STATUS" VARCHAR2(100 BYTE), 
    "LASTRUNTIME" DATE
   );
CREATE UNIQUE INDEX "D2C_EVENT_GENERATION_BOM_IND" ON ."D2C_EVENT_GENERATION_BOM" ("VIBKI");
--------------------------------------------------------
--  Constraints for Table D2C_EVENT_GENERATION_BOM
--------------------------------------------------------

ALTER TABLE "D2C_EVENT_GENERATION_BOM" MODIFY ("VIBKI" NOT NULL ENABLE);

下面是我的触发器PL/SQL:

create or replace TRIGGER "BL_D2C_EVENT_GENERATION_BOM" 
BEFORE INSERT
   ON D2C_EVENT_GENERATION_BOM REFERENCING NEW AS NEW OLD AS OLD 
FOR EACH ROW
BEGIN
    UPDATE D2C_EVENT_GENERATION_BOM SET vibki = :NEW.vibki, status = 'NEW', lastruntime = sysdate
            WHERE vibki = :NEW.vibki;
        IF ( sql%notfound ) THEN
            INSERT INTO D2C_EVENT_GENERATION_BOM (vibki,status,lastruntime)
            VALUES (:NEW.vibki,'NEW',sysdate);
    END IF;
END;

因为它看起来很简单,我只是第一次尝试更新,如果没有找到什么,请插入。

但此触发器不适用于此insert语句:

INSERT INTO D2C_EVENT_GENERATION_BOM (vibki) VALUES ('TAS2002/01');

例外情况:

Error starting at line : 1 in command -
INSERT INTO D2C_EVENT_GENERATION_BOM (vibki) VALUES ('TAS2002/01')
Error report -
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00036: maximum number of recursive SQL levels (50) exceeded

请大家支持我如何实现这个错误,我在另一个表上使用了相同的触发器,唯一的区别是我在upsert操作之前进行了一个select操作来收集一些值,所以我希望这段代码可以轻松工作,但它不起作用。

任何帮助都将不胜感激,非常感谢!

2 回复 | 直到 7 年前

Arkadiusz Åukasiewicz 7 年前

第一 sql%rowcount 而不是 sql%notfound 。对于更新sql%notfound alwasy为false。
其次,触发器是递归的。
触发器位于表D2C\u EVENT\u GENERATION\u BOM上
1、如果sql%rowcount=0,则插入D2C\u EVENT\u GENERATION\u BOM
2.insert(从点1)执行触发器,但仍然存在sql%rowcount=0,然后insert。。。

这个模式重复了50次。

要修复它,您必须通知下一个触发器来中断递归。最简单的方法是添加前缀并检查它。

create or replace TRIGGER "BL_D2C_EVENT_GENERATION_BOM" 

    BEFORE INSERT
       ON D2C_EVENT_GENERATION_BOM REFERENCING NEW AS NEW OLD AS OLD 
    FOR EACH ROW
    BEGIN
      if :NEW.vibki not like '-%' then  
        UPDATE D2C_EVENT_GENERATION_BOM SET vibki = :NEW.vibki, status = 'NEW', lastruntime = sysdate
                WHERE vibki = :NEW.vibki;            
            IF ( sql%rowcount = 0 ) THEN
                INSERT INTO D2C_EVENT_GENERATION_BOM (vibki,status,lastruntime)
                VALUES ('-'||:NEW.vibki,'NEW',sysdate);
        END IF;
      else 
       :NEW.vibki := substr(:NEW.vibki,2);
      end if; 
    END;

解决方案2)带视图和触发器

create view V_D2C_EVENT_GENERATION_BOM as (select * from  D2C_EVENT_GENERATION_BOM);

CREATE OR REPLACE TRIGGER T_D2C_EVENT_GENERATION_BOM
INSTEAD OF INSERT
ON V_D2C_EVENT_GENERATION_BOM
FOR EACH ROW
begin 
   UPDATE D2C_EVENT_GENERATION_BOM SET vibki = :NEW.vibki, status = 'NEW', lastruntime = sysdate
            WHERE vibki = :NEW.vibki;
        IF ( sql%rowcount = 0 ) THEN
            INSERT INTO D2C_EVENT_GENERATION_BOM (vibki,status,lastruntime)
            VALUES (:NEW.vibki,'NEW',sysdate);
    END IF;  
end;

INSERT INTO V_D2C_EVENT_GENERATION_BOM (vibki) VALUES ('11TAS2002/01');

解决方案3)需要合并0触发器

merge into D2C_EVENT_GENERATION_BOM a
 using (select 'your_id' vibki  from dual) b on (a.vibki =b.vibki)
WHEN MATCHED THEN 
 UPDATE set status = 'NEW', lastruntime = sysdate
            WHERE a.vibki = b.vibki
WHEN not MATCHED THEN 
 insert (vibki) values( b.vibki)         ;

Wernfried Domscheit 7 年前

您的触发器有几个问题:

对于 BEFORE INSERT 触发器不能有任何 OLD 价值
UPDATE [...] SET vibki = :NEW.vibki [...] WHERE vibki = :NEW.vibki 似乎没用
IF sql%notfound THEN 在这种情况下永远不可能是真的。

我不知道你想实现什么,但可能很简单

create or replace TRIGGER BL_D2C_EVENT_GENERATION_BOM
   BEFORE INSERT ON D2C_EVENT_GENERATION_BOM 
   FOR EACH ROW
BEGIN
    :NEW.status := 'NEW';
    :NEW.lastruntime := sysdate;
END;

REFERENCING NEW AS NEW OLD AS OLD 是默认值,因此可以跳过它。