Re: trigger error "mutating table"

Vanessa McCauley schrieb in Nachricht
<6r027d$hpo$1_at_oak.prod.itd.earthlink.net>...
>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
>
>
>

Take a look at the ORACLE Application developer's guide. I quote:

"Mutating and Constraining Tables
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress. Tables are never considered mutating or constraining for statement triggers unless the trigger is fired as the result of a DELETE CASCADE. For all row triggers, or for statement triggers that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating and constraining tables. These restrictions prevent a trigger from seeing an inconsistent set of data.

The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement. The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement. There is an exception to this restriction; BEFORE ROW and AFTER ROW triggers fired by a single row INSERT to a table do not treat that table as mutating or constraining. Note that INSERT statements that may involve more than one row, such as INSERT INTO emp SELECT . . ., are not considered single row inserts, even if they only result in one row being inserted..." (contd.)

Hope this makes things more clear
Frank Received on Fri Aug 14 1998 - 06:29:05 CDT