4
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'

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
mahesh soni
  • 43
  • 1
  • 3
  • That error occurs when you try to access the table the trigger is based on in a FOR EACH ROW trigger. Are you sure you have not put FOR EACH ROW in your trigger definition? – Tony Andrews Jun 08 '10 at 11:18
  • Dear Mr Tony, After removing FOR EACH ROW, error is not occurring but Records are not updating as specified in the trigger body. Kindly usggest. MaheshA.... – mahesh soni Jun 08 '10 at 11:55
  • What is the trigger supposed to do? It would appear to update some rows if cursor c1 returns a row. – Tony Andrews Jun 08 '10 at 12:04
  • this trigger will update the column COMPSTATUS & REMARK of table TEST_TRNCOMPMST if cursor c1 returns a row. But it is not updating even after c1 returns a row. Kindly suggest. MaheshA... – mahesh soni Jun 08 '10 at 12:10
  • Also note that your trigger is only doing one fetch, so if the cursor returns more than one record, only the first one gets updated. – AndyDan Jun 08 '10 at 18:52

2 Answers2

4

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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • Sorry . I forgot to type it in given example. after removing it from trigger definition error is not occurring but record is also not updating as per specified in trigger body. Kindly suggest. MaheshA... – mahesh soni Jun 08 '10 at 12:36
  • My first suggestion would be that you put some debug messages in the trigger e.g. using DBMS_OUTPUT.PUT_LINE to verify (a) that it is executing at all, and (b) whether the cursor is finding any rows. – Tony Andrews Jun 08 '10 at 13:14
0

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.

Rene
  • 10,391
  • 5
  • 33
  • 46
  • 1
    YES Sir, this is the only trigger I have. No another trigger is there on table TEST_TRNCOMPMST. Wht should I do, to update the table TEST_TRNCOMPMST, whenever the record, inserting in the table TEST_TRNCOMPVISIT, having compno which is there in table TEST_TRNCOMPMST. Kidnly help me, as I am fresher in ORACLE, I dont have any work experience in this field, only 2 months before I completed my studies. MaheshA... – mahesh soni Jun 10 '10 at 04:48