trigger to update insert into History table

From: Richard Brust <richard_brust_at_yahoo.com>
Date: 26 Feb 2003 17:29:23 -0800
Message-ID: <8b15ae11.0302261729.3dd3431f@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 Wed Feb 26 2003 - 19:29:23 CST

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