trigger error - mutating table???
From: Vanessa McCauley <vanessam_at_baylogics.com>
Date: Thu, 13 Aug 1998 18:00:20 -0700
Message-ID: <6r0286$hqu$1_at_oak.prod.itd.earthlink.net>
[Quoted] I am trying to construct a very simple trigger AFTER INSERT of a parent [Quoted] 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 Fri Aug 14 1998 - 03:00:20 CEST
Date: Thu, 13 Aug 1998 18:00:20 -0700
Message-ID: <6r0286$hqu$1_at_oak.prod.itd.earthlink.net>
[Quoted] I am trying to construct a very simple trigger AFTER INSERT of a parent [Quoted] 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 Fri Aug 14 1998 - 03:00:20 CEST