This worked perfectly - thanks much!
Holger Baer <holger.baer_at_science-computing.de> wrote in message news:<3E5DC663.4050301_at_science-computing.de>...
> You cannot select from the table the trigger is defined on. (That's what
> the error message is telling you). And you don't need to as well:
>
> 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
> 13 where Status.ID = :old.ID;
> 14* end;
>
> Regards,
>
> Holger
>
> Richard Brust wrote:
> > 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 Thu Feb 27 2003 - 11:30:10 CST
