Oracle upsert trigger error "ORA-00036: maximum number of recursive SQL levels (50) exceeded"

Question

I have been working on a simple upsert trigger that is using unique index related to one column called vibki.

Table DLL info for easy access :

  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);

Here is my trigger 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;

As it seems and quite simple, i am only firstly trying to update, if something has not found then please insert.

But this trigger doesn't work with this insert statement :

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

Exception :

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

Can please someone support how i can achieve this error, i was using same trigger on another table, only difference was i was making a select operation to gather some values before upsert operation, so i am expecting this code to easily work but it is not working.

Any helps would be appreciated, many thanks!

Arkadiusz Łukasiewicz · Accepted Answer · 2018-03-08 15:39:56Z

3

First sql%rowcount instead of sql%notfound. For update sql%notfound alwasy is false.
Second your trigger is recursive.
Trigger is on table D2C_EVENT_GENERATION_BOM
1. If sql%rowcount =0 then insert D2C_EVENT_GENERATION_BOM
2. trigger is execute for insert (from point 1) and still there is sql%rowcount = 0 then insert ...

And this schema is repeated 50 times.

To fix it you have to notify next trigger to break recursion. Easiest way is add prefix to and checking it.

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;

Solution 2) with view and trigger

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');

solution 3) Merge 0 triggers is needed

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)         ;

edited Mar 8, 2018 at 15:39

answered Mar 8, 2018 at 14:19

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

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

10 Comments

Hayra Over a year ago

This is creating unique constraint viaolation :/ INSERT INTO D2C_EVENT_GENERATION_BOM (vibki) VALUES ('TAS2002/01') Error report - ORA-00001: unique constraint (PSBD.D2C_EVENT_GENERATION_BOM_IND) violated

Arkadiusz Łukasiewicz Over a year ago

Ok I probably know what are you trying to achieve

Boneist Over a year ago

Solution 3: don't use a trigger and use a Merge statement instead gets my vote!

Hayra Over a year ago

Can you please describe why special view is needed in Solution 2 ?

Wernfried Domscheit Over a year ago

For solution 1) I don't understand the reason for if :NEW.vibki not like '-%' then. Also I assume this trigger would fail with ORA-04091: table D2C_EVENT_GENERATION_BOM is mutating... solution 2) and solution 3) are much better.

|

Wernfried Domscheit · Accepted Answer · 2018-03-08 16:08:19Z

0

Your trigger has several issues:

For BEFORE INSERT trigger you cannot have any OLD value.
UPDATE [...] SET vibki = :NEW.vibki [...] WHERE vibki = :NEW.vibki seems to be useless
IF sql%notfound THEN can never be TRUE in this condition.

I don't know what you try to achieve but it might be simply

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 is the default, so you can skip it.

edited Mar 8, 2018 at 16:08

answered Mar 8, 2018 at 14:20

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

6 Comments

Hayra Over a year ago

This is not compiled, because :NEW can't be edited... Thanks for your kind comments but "IF sql%notfound THEN" is valid syntax. Please check docs.oracle.com/cd/B10501_01/appdev.920/a96624/13_elems48.htm

Hayra Over a year ago

no worries. But it doesn't work for me as expected. Expectation is insert the row into table if the row doesn't exist. If row is already there, just make update set status = 'NEW' and lastupdatetime to system date. Currently it inserts but update is not as expected.

Wernfried Domscheit Over a year ago

It is contradicting when you say "insert into if row does not exist" When you have a insert trigger then the inserted row must exist always (otherwise it would not be an INSERT). If the insert fails for whatever reason, e.g. because your try to insert NULL for VIBKI then the trigger does not fire. Again, for me it is not clear what is the intention of this trigger. Please explain your business logic.

Hayra Over a year ago

Of course, let me try to simply tell it to you again. We have a batch operation system that executes insert operations on this table, regarding to these 3 columns(VIBKI(PK),STATUS,LASTRUNTIME), and we will make one application that will listen status with "NEW" values and make some operations, but to handle new row updates into table, we want to use simple trigger even that our batch consumer creates INSERT operation we will firstly check if there is existing row already, then only update LASTRUNTIME and STATUS. If there is no, check will show that we should insert not update.

Wernfried Domscheit Over a year ago

Please edit your question instead of putting additional information as comment.

|

Collectives™ on Stack Overflow

Oracle upsert trigger error "ORA-00036: maximum number of recursive SQL levels (50) exceeded"

2 Answers 2

10 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related