Peter Kreemers
06-26-02, 05:19
| 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; / :confused: |