触发器已创建，但在插入新条目后无法工作。ORA-04091错误显示

Question

我创建了触发器，以便根据来自另一个表的条目将数据插入到另一个表中。将创建触发器，但在表中输入值后，将收到以下错误。

ORA-04091:表OOMUSER.EMTN_EVC_PAIR_STATUS正在变异，触发器/函数可能看不到它 ORA-06512:在"OOMUSER.PORTSTATUSUPDATE"，第22行 ORA-04088:执行触发器“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;
/

Answer 1

您的错误消息是第22行的问题所在：

ORA-06512:在"OOMUSER.PORTSTATUSUPDATE"，第22行

找出触发体源代码的行号并不是一门精确的科学，特别是当它被粘贴到StackOverflow中时，但是line 22似乎认为这一行是问题所在：

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

OOM_VW_EMTN_DUALEVC_STATUS是一个视图吗？如果是的话，它所依赖的是哪些表？我猜是EMTN_EVC_PAIR_STATUS的特性在那里。