problem with trigger mutation

[rajabalachandra]

hi,

i have a table t1 with columns c1 and c2.

i need to set the value of c2 based on the value of c1.
so many applications are updating the column c1.
the column c2 is newly added for some other applications.
i can not modify all the old applications to update c2.

i have a trigger on the table to update
the column c2 if the value of column c1
is changed.

when i tried to update the column c1
i am getting the error

" ORA-04091 table string.string is mutating,
trigger/function may not see it "

copy of the trigger is

create or replace trigger UpdateExpectedStock
after insert or update on DrugControl
referencing old as O new as N
for each row
begin

update drugcontrol set ExpectedStock=
ExpectedStock-(O.BalanceDrug - N.BalanceDrug)
where Deptcode=O.DeptCode
and TradeCode=O.TradeCode;
end;


can any body help to solve this problem.


thanks in advance
Raja

[rotem_fo]

ORA-4091 Mutating Table Explanation and Workarounds:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=74859.1

[gbrabham]

Change your trigger to a before insert

create or replace trigger UpdateExpectedStock
BEFORE insert or update on DrugControl
referencing old as O new as N
for each row

BEGIN

...
IF updating('C1')
then
:N.ExpectedStock=
ExpectedStock-(O.BalanceDrug - N.BalanceDrug)
where Deptcode=O.DeptCode
and TradeCode=O.TradeCode;
end if;

hth
Gregg

[rajabalachandra]

hi

i got it working.

thank you gbrabham.

thank you everybody

-Raja