Re: Error: Table Mutating, Trigger may not see it???

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 14 Aug 1998 17:01:03 GMT
Message-ID: <35e56b17.187403942@192.86.155.100>

A copy of this was sent to "Todd Weaver" <tweaver_at_imsisoft.com> (if that email address didn't require changing) On Thu, 13 Aug 1998 17:55:21 -0700, you wrote:

>I am trying to construct a very simple trigger AFTER INSERT of a parent
>record, to populate the child record's FK field.....
>
>On INSERT to FACILITIES, the application first generates a random surrogate
>PK in the REGISTRY table, then returns that value to act as the
>FACILITIES.PK value as well. All good.
>
>So, the trigger takes that PK value from FACILITIES, and kicks it back to
>the REGISTRY record's FK to FACILITIES. This has to happen AFTER INSERT,
>since the REGISTRY record is formally a child of the FACILITIES record.
>Hence:
>
>CREATE TRIGGER.....
>AFTER INSERT ON F_FACILITIES
>
>BEGIN
>INSERT INTO REGISTRY (FKEY_FACILITIES)
>VALUES (:NEW.PKEY)
>WHERE REGISTRY.PKEY = :NEW.PKEY;
>END;
>
>The error message states "f_facilities is mutating, the trigger may not see
>it..."
>

The way to do this is to defer reading the table until after all of the row level changes have been made. Below is an example. We use a package to maintain a state across the triggers. the first trigger, a BEFORE trigger, simply resets the package state to some know state. the second trigger collects all of the rowids affected by the update into a table. The third trigger contains all of the logic you want to perform for the affected rows. You will loop over the entries in the pl/sql table. An example with your tables follows:

SQL> create table f_facilities ( pkey int primary key ); Table created.

SQL> create table registry ( pkey int primary key references f_facilities ); Table created.

SQL> create or replace package state_pkg 2 as

  3      type ridArray is table of rowid index by binary_integer;
  4      rids    ridArray;
  5          empty   ridArray;

6 end;
7 /
Package created.

SQL> create or replace trigger f_facilities_bi   2 before insert on f_facilities
  3 begin
  4 state_pkg.rids := state_pkg.empty;   5 end;
  6 /
Trigger created.

SQL> create or replace trigger f_facilities_aifer   2 after insert on f_facilities
  3 for each row
  4 begin
  5 state_pkg.rids( state_pkg.rids.count+1 ) := :new.rowid;   6 end;
  7 /
Trigger created.

SQL> create or replace trigger f_facilities_ai 2 after insert on f_facilities
3 begin

  4      for i in 1 .. state_pkg.rids.count loop
  5                  insert into registry
  6                  select pkey from f_facilities where rowid =
state_pkg.rids(i);
  7      end loop;

8 end;
9 /
Trigger created.

SQL> select * from registry;
no rows selected

SQL> insert into f_facilities values ( 1 ); 1 row created.

SQL> select * from registry;

PKEY

>I cannot find a discussion of mutating tables (or even a definition) in any
>on-line ORACLE books, O'Reilly press books, or Oracle press books that I
>have. Any ideas?
>

The server application developers guide page 9-12 (in v7) page 13-13 (in v8) defines and explains how to deal with mutating tables.

>Thanks!
>-Veeb
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 14 1998 - 12:01:03 CDT