mutating, trigger/function may not see it- error during execution of trigger

Question

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 have made this trigger and while insert the row in table- TEST_TRNCOMPVISIT it gives following error-

The following error has occurred:

ORA-04091: table TEST.TEST_TRNCOMPVISIT is mutating, trigger/function may not see it ORA-06512: at "TEST.UPDATE_TEST_280510", line 4 ORA-06512: at "TEST.UPDATE_TEST_280510", line 10 ORA-04088: error during execution of trigger 'TEST.UPDATE_TEST_280510'

Kindly suggest over this. MaheshA.....

Answer 1

The "table is mutating" exception is raised when a trigger that is defined as FOR EACH ROW tries to access the table that is was fired for. Tom Kyte has written a great guide to the causes and resolution of this exception here.

In your posted example you do not have FOR EACH ROW and so I would not expect the exception to be raised. Usually one only needs to use FOR EACH ROW triggers in cases where it is necessary to access the :OLD or :NEW values of each row, which you are not.

Answer 2

Is this the only trigger you have? Your trigger updates table TEST_TRNCOMPMST. If there is a trigger on this table that accesses TEST_TRNCOMPVISIT you get the error message.