Mutation, trigger / function may not see it - error during trigger start

CREATE OR REPLACE TRIGGER UPDATE_TEST_280510
AFTER insert on TEST_TRNCOMPVISIT
declare
V_TRNCOMPNO NUMBER(10);

CURSOR C1 IS SELECT B.COMPNO FROM TEST_TRNCOMPVISIT A, TEST_TRNCOMPMST B, 
                                  TEST_MEMMAST C
WHERE A.COMPNO=B.COMPNO 
AND B.TRNMEMID=C.MEMID 
AND C.MEMOS>=1000;

begin
open c1;
fetch c1 into V_TRNCOMPNO;


UPDATE TEST_TRNCOMPMST SET COMPSTATUS='P',
       remark='comp is pending due to O/S>1000'
WHERE COMPNO=V_TRNCOMPNO AND COMPSTATUS='C';
CLOSE C1;

end;

I made this trigger and insert the row into the table - TEST_TRNCOMPVISIT, it gives the following error -

The following error has occurred:

ORA-04091: table TEST.TEST_TRNCOMPVISIT mutates, trigger / function may not see it
ORA-06512: on page "TEST.UPDATE_TEST_280510", line 4
ORA-06512: on page "TEST.UPDATE_TEST_280510", line 10
ORA-0408810 error: during trigger "TEST.UPDATE_TEST_280510"

+3
source share
2 answers

, ? TEST_TRNCOMPMST. , TEST_TRNCOMPVISIT, .

0

"table is mutating" , , FOR EACH ROW, , . .

FOR EACH ROW, , . FOR EACH ROW , OLD NEW: , .

+4

Source: https://habr.com/ru/post/1749000/


All Articles