Re: trigger to update insert into History table

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 27 Feb 2003 09:03:47 +0100
Message-ID: <3E5DC663.4050301@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 - 02:03:47 CST

Re: trigger to update insert into History table - cannot get to work