CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggers
Tiny Jaguar
Tiny Jaguar

Reputation: 433

Trigger created but not working after insertion of new entry. ORA-04091 error shown

I have created trigger to insert data into another table based on entry from another table. The trigger is created but after entering value in the table, below error is received.

ORA-04091: table OOMUSER.EMTN_EVC_PAIR_STATUS is mutating, trigger/function may not see it

ORA-06512: at "OOMUSER.PORTSTATUSUPDATE", line 22

ORA-04088: error during execution of trigger 'OOMUSER.PORTSTATUSUPDATE'

DROP TRIGGER OOMUSER.PORTSTATUSUPDATE;

CREATE OR REPLACE TRIGGER OOMUSER.PORTSTATUSUPDATE
AFTER INSERT 
ON OOMUSER.EMTN_EVC_PAIR_STATUS     FOR EACH ROW
DECLARE
LEGA VARCHAR2(20);
LEGB VARCHAR2(20);
ACTIVEPAIRA NUMBER;
ACTIVEPAIRB NUMBER;
BEGIN
SELECT EELA.CPE_PORT_SERVICE_ID INTO LEGA FROM EMTN_EVC_LEG_A EELA WHERE EELA.EMTN_EVC_PAIRID = :NEW.EMTN_EVC_PAIRID;
SELECT EELB.CPE_PORT_SERVICE_ID INTO LEGB FROM EMTN_EVC_LEG_B EELB WHERE EELB.EMTN_EVC_PAIRID = :NEW.EMTN_EVC_PAIRID;

IF (:NEW.STATUS = 'Active' OR :NEW.STATUS = 'Planned') 
THEN    
    INSERT INTO OOMUSER.EMTN_ETN_CPE_PORT_STATUS
        (CPE_PORT_SERVICE_ID,STATUS,STATUS_START_DATETIME)
        VALUES(LEGA,:NEW.STATUS,SYSDATE);
    INSERT INTO OOMUSER.EMTN_ETN_CPE_PORT_STATUS
        (CPE_PORT_SERVICE_ID,STATUS,STATUS_START_DATETIME)
        VALUES(LEGB,:NEW.STATUS,SYSDATE);

ELSIF(:NEW.STATUS = 'Rejected' OR :NEW.STATUS = 'Cancelled')
THEN
    SELECT COUNT(OVEDS.EMTN_EVC_PAIRID) INTO ACTIVEPAIRA FROM OOM_VW_EMTN_DUALEVC_STATUS OVEDS WHERE OVEDS.EMTN_EVC_PAIRID IN 
    (
        SELECT EELA.EMTN_EVC_PAIRID FROM EMTN_EVC_LEG_A EELA WHERE EELA.CPE_PORT_SERVICE_ID = 
        (
            SELECT EELA.CPE_PORT_SERVICE_ID FROM EMTN_EVC_LEG_A EELA WHERE EELA.EMTN_EVC_PAIRID = :NEW.EMTN_EVC_PAIRID
        )
    ) AND UPPER(OVEDS.STATUS) IN ('ACTIVE','PLANNED'); 
    
    IF(ACTIVEPAIRA = 0) 
    THEN
        INSERT INTO OOMUSER.EMTN_ETN_CPE_PORT_STATUS
            (CPE_PORT_SERVICE_ID,STATUS,STATUS_START_DATETIME)
            VALUES(LEGA,:NEW.STATUS,SYSDATE);
    END IF;
    
    SELECT COUNT(OVEDS.EMTN_EVC_PAIRID) INTO ACTIVEPAIRB FROM OOM_VW_EMTN_DUALEVC_STATUS OVEDS WHERE OVEDS.EMTN_EVC_PAIRID IN 
    (
        SELECT EELB.EMTN_EVC_PAIRID FROM EMTN_EVC_LEG_B EELB WHERE EELB.CPE_PORT_SERVICE_ID = 
        (
            SELECT EELB.CPE_PORT_SERVICE_ID FROM EMTN_EVC_LEG_B EELB WHERE EELB.EMTN_EVC_PAIRID = :NEW.EMTN_EVC_PAIRID
        )
    ) AND UPPER(OVEDS.STATUS) IN ('ACTIVE','PLANNED'); 
    
    IF(ACTIVEPAIRB = 0) 
    THEN
        INSERT INTO OOMUSER.EMTN_ETN_CPE_PORT_STATUS
            (CPE_PORT_SERVICE_ID,STATUS,STATUS_START_DATETIME)
            VALUES(LEGB,:NEW.STATUS,SYSDATE);
    END IF;
    
END IF;

END;
/

Upvotes: 0

Views: 221

Answers (1)

APC
APC

Reputation: 146349

Your error message ayss that line 22 is the problem:

ORA-06512: at "OOMUSER.PORTSTATUSUPDATE", line 22

Figuring out the line numbers of Trigger Body source code is not a precise science, especially when it has been pasted into StackOverflow, but it seems plausible that line 22 indicates this line as the trouble:

SELECT COUNT(OVEDS.EMTN_EVC_PAIRID) INTO ACTIVEPAIRB FROM OOM_VW_EMTN_DUALEVC_STATUS OVEDS WHERE OVEDS.EMTN_EVC_PAIRID

Is OOM_VW_EMTN_DUALEVC_STATUS a view, by any chance? If so, what tables does it depend on? My guess is EMTN_EVC_PAIR_STATUS features in there.

Upvotes: 1

Related Questions