foreign key and triggers
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, AndreReceived on Mon Sep 19 1994 - 21:08:28 CEST