Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: mutating table, trigger error

    hello,

    I have written a trigger that fires before I delete a row from my table Billet. It compiles fine, but when I try to delete a row from Billet, I get the "table mutation, trigger may not see it" error and the mutation is on a different table, Person_designate. Is that right? Is something else wrong? are two cursors not allowed in a trigger or something like that?

    Here's the beginning part of my trigger:
    CREATE OR REPLACE TRIGGER TRIG_deleteBillet
    BEFORE DELETE ON BILLET
    FOR EACH ROW
    DECLARE
    per_billet_error EXCEPTION;
    CurDate DATE := SYSDATE;
    personActive INTEGER := 0;
    CURSOR perBillEndLog IS
    SELECT D.ENDDATE, D.BILLID, D.PERSONID FROM
    PERSON_DESIGNATE D WHERE D.PERSONID IN (SELECT
    P.PERSONID FROM PERSON_DESIGNATE P WHERE P.BILLID
    = ld.BILLID);
    CURSOR personBillLog IS
    SELECT P.PERSONID, P.ENDDATE FROM PERSON_DESIGNATE P
    WHERE P.BILLID = ld.BILLID;


    and this is the error:
    SQL> DELETE FROM BILLET WHERE BILLID = 10;
    DELETE FROM BILLET WHERE BILLID = 10
    *
    ERROR at line 1:
    ORA-04091: table U10966347.PERSON_DESIGNATE is mutating, trigger/function may not see it
    ORA-06512: at "U10966347.TRIG_DELETEBILLET", line 8
    ORA-06512: at "U10966347.TRIG_DELETEBILLET", line 11
    ORA-04088: error during execution of trigger 'U10966347.TRIG_DELETEBILLET'

    Thanks.
    Jaime

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    The trigger is attempting to look at or modify the table that was in the middle of being modified by the statement which fired it.

    Rewrite the trigger , so it does not read that table.
    SATHISH .

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    so if the Person_designate table has a foreign key with ON DELETE CASCADE from Billet, then the Delete statement will be trying to cascade the delete to the Person_designate table, which causes the mutation error?

    is there any way to get around the error? I can't think of a way for me to make the trigger without accessing the Person_designate table. I thought the new and old references were supposed to avoid the table mutation error?

    thanks again,
    jaime

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: mutating table, trigger error

    Refer http://dbforums.com/arch/11/2002/6/415216
    You probably have a similar problem.


    Originally posted by jk02
    hello,

    I have written a trigger that fires before I delete a row from my table Billet. It compiles fine, but when I try to delete a row from Billet, I get the "table mutation, trigger may not see it" error and the mutation is on a different table, Person_designate. Is that right? Is something else wrong? are two cursors not allowed in a trigger or something like that?

    Here's the beginning part of my trigger:
    CREATE OR REPLACE TRIGGER TRIG_deleteBillet
    BEFORE DELETE ON BILLET
    FOR EACH ROW
    DECLARE
    per_billet_error EXCEPTION;
    CurDate DATE := SYSDATE;
    personActive INTEGER := 0;
    CURSOR perBillEndLog IS
    SELECT D.ENDDATE, D.BILLID, D.PERSONID FROM
    PERSON_DESIGNATE D WHERE D.PERSONID IN (SELECT
    P.PERSONID FROM PERSON_DESIGNATE P WHERE P.BILLID
    = ld.BILLID);
    CURSOR personBillLog IS
    SELECT P.PERSONID, P.ENDDATE FROM PERSON_DESIGNATE P
    WHERE P.BILLID = ld.BILLID;


    and this is the error:
    SQL> DELETE FROM BILLET WHERE BILLID = 10;
    DELETE FROM BILLET WHERE BILLID = 10
    *
    ERROR at line 1:
    ORA-04091: table U10966347.PERSON_DESIGNATE is mutating, trigger/function may not see it
    ORA-06512: at "U10966347.TRIG_DELETEBILLET", line 8
    ORA-06512: at "U10966347.TRIG_DELETEBILLET", line 11
    ORA-04088: error during execution of trigger 'U10966347.TRIG_DELETEBILLET'

    Thanks.
    Jaime
    Oracle can do wonders !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •