触发器已创建，但在插入新条目后不起作用.显示 ORA-04091 错误

Question

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

ORA-04091：表OOMUSER。EMTN_EVC_PAIR_STATUS正在变异，触发器/功能可能看不到它

ORA-06512：在"OOMUSER.端口状态更新"，第 22 行

ORA-04088：执行触发器"OOMUSER"时出错。端口状态更新'

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.端口状态更新"，第 22 行

弄清楚 Trigger Body 源代码的行号并不是一门精确的科学，尤其是当它被粘贴到 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功能。