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

Hi !
You can't perform SELECT, INSERT, DELETE, UPDATE on table in it's own trigger. Because table is "mutating" during all modify operation (includes BEFORE and AFTER trigger execution).
When you try to insert records into dependent table, ORACLE must check existance of parent record. But parent table is mutating during trigger execution. This causes error.

Best Regards. Maxim.

Todd Weaver 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..."
>
>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?
>
>Thanks!
>-Veeb
>
>
Received on Mon Aug 17 1998 - 06:06:40 CDT