foreign key and triggers

From: -yoshida a.w. <awy_at_wisny.mv.att.com>
Date: Mon, 19 Sep 1994 19:08:28 GMT
Message-ID: <CwE565.3ID_at_nntpa.cb.att.com>


Consider the following SQL:

CREATE TABLE x ( a VARCHAR2(10) CONSTRAINT pk_x PRIMARY KEY); CREATE TABLE y ( x VARCHAR2(10) CONSTRAINT fk_x REFERENCES x(a));

CREATE OR REPLACE TRIGGER x_update_trig
BEFORE UPDATE ON x
FOR EACH ROW
BEGIN
        UPDATE y SET x = :new.a WHERE x = :old.a; END;
/

The intended purpose is to, as a side-effect, update the child table y when the parent table x is updated. However, this results in a "mutating table" error from Oracle because y references x. Is it only possible to achieve the desired affect by dropping the foreign key constraint and manage everything (including referential integrity) with triggers?

I suppose one could temporarily disable the foreign key constraint but won't this cause a window of vulnerability by other transactions and make recovery difficult within the trigger?

				Thanks in advance,
				Andre
Received on Mon Sep 19 1994 - 21:08:28 CEST

Original text of this message