1 create or replace trigger Hist_trig
2 BEFORE update or delete on Parts
3 FOR EACH ROW
4 begin
5 insert into PartsHistory
6 select StatusName, --this is from t2
7 :old.PartNo, --all other from t1
8 :old.StatusID,
9 :old.Memo,
10 :old.LastUpdateOn,
11 :old.UpdatedBy
12 --from Status, Parts -- t2 and t1
13 -- where Parts.ID = Status.ID;
from Status
where Status.id = :old.id;
14* end;
"Richard Brust" <richard_brust_at_yahoo.com> wrote in message
news:8b15ae11.0302261729.3dd3431f_at_posting.google.com...
> I have two tables "related" by an ID field. [t1 has 6 fields, t2 has
> two.] First field of both is the ID.
>
> When I update t1, I want all the values from t1 (except the ID) and
> the second field (not ID) from t2 to be inserted in to a History
> table.
>
> One of the versions of the trigger, that compiles, but does not work
> is:
> 1 create or replace trigger Hist_trig
> 2 BEFORE update or delete on Parts
> 3 FOR EACH ROW
> 4 begin
> 5 insert into PartsHistory
> 6 select StatusName, --this is from t2
> 7 :old.PartNo, --all other from t1
> 8 :old.StatusID,
> 9 :old.Memo,
> 10 :old.LastUpdateOn,
> 11 :old.UpdatedBy
> 12 from Status, Parts -- t2 and t1
> 13 where Parts.ID = Status.ID;
> 14* end;
>
> I have tried other permutations/bastardizations, but they all give the
> error:
> SQL> update Parts
> 2 set partno = 'bar'
> 3 where partno = 'foo';
> update Parts
> *
> ERROR at line 1:
> ORA-04091: table PARTS is mutating, trigger/function may not see it
> ORA-06512: at "HIST_TRIG", line 2
> ORA-04088: error during execution of trigger 'HIST_TRIG'
>
> This worked when all the field came just from the Parts table (t1),
> but a new req came up to include the StatusName from t2, and I'm
> stumped.
>
> Thanx for any assistance!
Received on Mon Mar 03 2003 - 05:55:36 CST
