Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    1

    Red face Unanswered: ORA-04091: Table A is mutating, trigger/function may not see it

    Hi there,

    I have a problem with a trigger I defined (see code below) and wondered whether somebody of you Oracle gurus knows a solutions to this problem.

    Let me first explain what I want to do with the trigger:

    * I have 2 tables: Move_Task and V_Dock_Shelter with a 1 to many relationship. To 1 V_Dock_Shelter record 0,1 or more Move_Task records can be related.

    +-------------+ N : 1 +-------------------+
    | Move_Task | <<---------------------> | V_Dock_Shelter |
    +-------------+ +-------------------+

    * The primary unique key of V_Dock_Shelter is the combination of Load_Key en Load_Detail_Key. These fields can also be found in the Move_Task table as V_Load_Key and V_Load_Detail_Key (non-unique).

    * The primary unique key of the Move_Task table is the field Key.

    * Both the Move_Task table and the V_Dock_Shelter have a Status field.

    * If the status of one of a Move_Tasks changes, I want the trigger to change the Status of the related V_Dock_Shelter record automatically.
    This goes fine except for the 'Complete' status.
    If the Status of one of the Move_Task records changes to 'Complete' I want to check whether there is any Move_Task record for the related V_Dock_Shelter record that still has a Status unequal to 'Complete'. If ALL Move_Task records have a Status 'Complete' then I want to set the Status of the related V_Dock_Shelter record to 'Ready' too.
    To check the number of Move_Task records that have a Status unequal to 'Complete', I use a cursor. Opening the cursor gives me the problem:

    ORA-04091: table DCSDBA.MOVE_TASK is mutating, trigger/function may not see it

    Does anyone know a solution for this problem or a workaround.
    Thank you.


    create or replace trigger V_DOCK_SHELTER_STATUS
    before update of Status on Move_Task
    for each row
    declare

    cursor NonCompleteCount
    is
    select count(*)
    from Move_Task
    where V_Load_Key = :new.V_Load_Key
    and V_Load_Detail_Key = :new.V_Load_Detail_Key
    and Key <> :new.Key
    and NVL(Status,'X') <> 'Complete'
    ;

    l_Counter integer := 0;

    begin

    if :new.V_Load_Key is not NULL and
    :new.V_Load_Detail_Key is not NULL
    then

    if :new.Status = 'Released' then

    update V_Dock_Shelter
    set Status = 'Move Task Released',
    MT_Released_Flag = 'Y',
    MT_Released_Dstamp = SYSDATE
    where Load_Key = :new.V_Load_Key
    and Load_Detail_Key = :new.V_Load_Detail_Key
    and NVL(Status,'X') not in ('Released',
    'Move Task Error');

    elsif :new.Status = 'Error' then

    update V_Dock_Shelter
    set Status = 'Move Task Error'
    where Load_Key = :new.V_Load_Key
    and Load_Detail_Key = :new.V_Load_Detail_Key
    and NVL(Status,'X') not in ('Move Task Error');

    elsif :new.Status = 'Complete' then

    open NonCompleteCount;
    fetch NonCompleteCount into l_Counter;
    close NonCompleteCount;
    if NVL(l_Counter,0) = 0 then
    update V_Dock_Shelter
    set Status = 'Ready',
    Ready_Flag = 'Y',
    Ready_Dstamp = SYSDATE
    where Load_Key = :new.V_Load_Key
    and Load_Detail_Key = :new.V_Load_Detail_Key;
    end if;

    end if;

    end if; if :new.V_Load_Key is not NULL and ...

    exception
    when no_data_found then
    NULL;

    end;
    /


  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb

    Hello,

    the problem is that you select datas out of move_task and want to update fields in the same table in one trigger.

    The solution is to select all datas from move_task in a before statement trigger and put the result in package variables. After that select the stored datas out of the package variables in the before update trigger.

    Hope that helps ?

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

Posting Permissions

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