Results 1 to 11 of 11

Thread: trigger error

  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: trigger error

    hi ,

    when i try to insert a record it gives the following error

    ERROR at line 1:
    ORA-04091: table SCOT.CNT_Centalproc is mutating, trigger/function may not see it
    ORA-06512: at "SCOT.TR_INS_UpdDt", line 3
    ORA-04088: error during execution of trigger 'SCOT.TR_INS_UpdDt'


    Create or Replace Trigger TR_INS_UpdDt
    After insert On CNT_Centalproc
    For Each Row
    Begin
    If :New.CNT_CNT_NUMBER = 3 then
    Update CNT_Centalproc set CNT_ProcCLOSE=SYSDATE,
    CNT_ProcState= 'Close'
    Where CNT_Centalproc.CNT_MAIN_ID = :New.CNT_SUB_ID
    end if;

    end;
    /



    when i try to insert a record it gives the following error

    ERROR at line 1:
    ORA-04091: table SCOT.CNT_Centalproc is mutating, trigger/function may not see it
    ORA-06512: at "SCOT.TR_INS_UpdDt", line 3
    ORA-04088: error during execution of trigger 'SCOT.TR_INS_UpdDt'


    field CNT_SUB_ID is referencing the CNT_MAIN_ID. This table has multiple records
    and the field CNT_SUB_ID references the CNT_MAIN_ID. every multiple record will
    reference the first record of CNT_MAIN_ID.

    thanx.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is impossible to update a record through a trigger that fires on inserting into the same table.

    There where several question about it on this forum; use a "Search" function (you'll find this button on the top of every forum page) and you'll find the solution to the problem.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Littlefoot
    It is impossible to update a record through a trigger that fires on inserting into the same table.

    There where several question about it on this forum; use a "Search" function (you'll find this button on the top of every forum page) and you'll find the solution to the problem.
    While that is true, he can do what he was trying to do with the following code.

    Create or Replace Trigger TR_INS_UpdDt
    After insert On CNT_Centalproc
    For Each Row
    Begin
    If :New.CNT_CNT_NUMBER = 3 then
    :new.CNT_ProcCLOSE=SYSDATE,
    :new.CNT_ProcState= 'Close'
    end if;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    hi,

    Thanx for your replies.


    The table would look like this


    CNT_MAIN_ID CNT_SUB_ID CNT_CNT_NUMBER CNT_ProcCLOS CNT_ProcState

    1 1 1 Active
    2 2 1 Active
    3 1 2 Active
    4 1 3 Active


    Now when the CNT_MAIN_ID = 4 record is inserted, the trigger
    shou;d update the CNT_ProcCLOSE=sysdate and change the
    CNT_ProcState=Close for the CNT_MAIN_ID=1 (which is CNT_MAIN_ID=CNT_SUB_ID)

    thanx again.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Then try the following.

    Create or Replace Trigger TR_INS_UpdDt
    After insert On CNT_Centalproc
    For Each Row
    PRAGMA AUTONOMOUS_TRANSACTION;
    Begin
    If :New.CNT_CNT_NUMBER = 3 then
    Update CNT_Centalproc set CNT_ProcCLOSE=SYSDATE,
    CNT_ProcState= 'Close'
    Where CNT_Centalproc.CNT_MAIN_ID = :New.CNT_SUB_ID
    end if;
    end;

    Please be aware that the pragma only works on oracle 8i and above.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    hi,

    still it is not working

    it gives the following error

    ORA-06519: active autonomous transaction detected and rolled back

    thanx.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sorry, My fault I forgot the commit.

    Create or Replace Trigger TR_INS_UpdDt
    After insert On CNT_Centalproc
    For Each Row
    PRAGMA AUTONOMOUS_TRANSACTION;
    Begin
    If :New.CNT_CNT_NUMBER = 3 then
    Update CNT_Centalproc set CNT_ProcCLOSE=SYSDATE,
    CNT_ProcState= 'Close'
    Where CNT_Centalproc.CNT_MAIN_ID = :New.CNT_SUB_ID
    commit;
    end if;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Using AUTONOMOUS_TRANSACTION in triggers is nearly always a bad idea. In this case, for example, the main record stays set to 'Closed' even if the main transaction is rolled back so that the record with CNT_CNT_NUMBER = 3 never actually existed.

    Use the more tricky but correct standard work-around of statement-level triggers and a package to save state - best described here:

    http://asktom.oracle.com/~tkyte/Mutate/index.html

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    don't create triggers that update the same table and data that the trigger
    fired upon!
    poo on you!

    one of my wish-lists is that Oracle creates an "AFTER-COMMIT" trigger.

    INSTEAD, why not pass your insert statement into a PACKAGE/PROCEDURE?

    this way, the procedure can check/validate the data and insert/update appropriately
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    thanx beilstwh. after i put commit it worked. but the only problem would be
    andrewst said, if the main transaction is rolled back.

    thanx.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    I still say to make an insert procedure that inserts and updates what you
    want instead of the trigger.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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