Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!cyclone1.gnilink.net!wn14feed!worldnet.att.net!204.127.198.203!attbi_feed3!attbi_feed4!attbi.com!sccrnsc04.POSTED!not-for-mail
From: "DessertDweller" <jdufour@attbi.com>
Newsgroups: comp.databases.oracle.server
References: <8b15ae11.0302261729.3dd3431f@posting.google.com>
Subject: Re: trigger to update insert into History table - cannot get to work
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <YoH8a.297163$2H6.5289@sccrnsc04>
NNTP-Posting-Host: 12.240.213.152
X-Complaints-To: abuse@attbi.com
X-Trace: sccrnsc04 1046692536 12.240.213.152 (Mon, 03 Mar 2003 11:55:36 GMT)
NNTP-Posting-Date: Mon, 03 Mar 2003 11:55:36 GMT
Organization: AT&T Broadband
Date: Mon, 03 Mar 2003 11:55:36 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.server:178246
X-Received-Date: Mon, 03 Mar 2003 04:55:29 MST (news.easynews.com)

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@yahoo.com> wrote in message
news: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!