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;
/

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