Constraining table error using a view

From: <pvraudin_at_my-dejanews.com>
Date: Thu, 20 Aug 1998 14:40:04 GMT
Message-ID: <6rhcg5$caa$1@nnrp1.dejanews.com>

Hi, everybody!

I encountered a strange Oracle behaviour. Below is a script illustrating the problem and a workaround.
Thank you in advance for any comments on this.

Pavel Raudin
IBT International, Samara, Russia
pvraudin_at_fian.samara.ru

/* Two tables (Parents and Childs) with an integrity constraint (foreign
key).

   Parents can be active or disabled.
   We want to notify active Parents on any change of its Childs.    We do not want to track status of disabled Parents.    We create a view to access only active Parents. PROBLEM:
   Use of a view in ON UPDATE trigger for the child table gives 'cannot modify constraining table' error.

   INSERT and DELETE triggers on the child table work with the view just fine.

   You can just run this script as is.
   I also placed here fragments of my SQL*Plus output marked with --# */
CREATE TABLE Parents (

    Parent_Id NUMBER(4) PRIMARY KEY,     Child_Modified NUMBER(1) default 0,     Is_Active NUMBER(1) default 1 /* a Parent can be active or disabled */
);
CREATE TABLE Childs (

    Child_Id     NUMBER(4)  PRIMARY KEY,
    Parent_Id     NUMBER(4),
    Attr     NUMBER

);
ALTER TABLE Childs ADD CONSTRAINT Childs_FK

    FOREIGN KEY (Parent_ID) REFERENCES Parents (Parent_ID);

/* This view shows only active Parents */
CREATE OR REPLACE VIEW Active_Parents AS SELECT *
FROM Parents
WHERE Is_Active = 1;

/* Insert a couple of Parents */

INSERT INTO Parents VALUES (1,0,1) /* active Parent */; INSERT INTO Parents VALUES (2,0,0) /* disabled Parent */;

/* We want to notify active Parents on any change of its Childs.

   We do not want to track status of disabled Parents. */
/* -------------------- Insertions ------------------------- */
CREATE OR REPLACE TRIGGER Childs_Inserted BEFORE INSERT ON Childs
FOR EACH ROW
BEGIN
    UPDATE Active_Parents
    SET Child_Modified = 1
    WHERE Parent_ID = :new.Parent_ID; END;
/

INSERT INTO Childs  VALUES (1,1,0);
INSERT INTO Childs  VALUES (2,1,0);
INSERT INTO Childs  VALUES (3,2,0);

SELECT * FROM Parents;
/* Works fine! */

--# PARENT_ID CHILD_MODIFIED  IS_ACTIVE
--#---------- -------------- ----------
--#        1              1          1
--#        2              0          0

/* .... Say, we've handled the notification and should reset the
modification flag */
UPDATE Active_Parents SET Child_Modified = 0;

/* -------------------- Deletions ------------------------- */
CREATE OR REPLACE TRIGGER Childs_Deleted BEFORE DELETE ON Childs
FOR EACH ROW
BEGIN
    UPDATE Active_Parents
    SET Child_Modified = 1
    WHERE Parent_ID = :old.Parent_ID; END;
/

DELETE FROM Childs WHERE Child_ID IN (2,3); SELECT * FROM Parents;
/* Works fine! */

--# PARENT_ID CHILD_MODIFIED  IS_ACTIVE
--#---------- -------------- ----------
--#        1              1          1
--#        2              0          0

/* .... Say, we've handled the notification and should reset the
modification flag */
UPDATE Active_Parents SET Child_Modified = 0;

/* -------------------- Updates ------------------------- */
/* Notify active Parents on Child's attributes change.

   Try to use the Active_Parents view.
*/
CREATE OR REPLACE TRIGGER Childs_Changed BEFORE UPDATE OF Attr, Parent_Id ON Childs FOR EACH ROW
BEGIN
    UPDATE Active_Parents
    SET Child_Modified = 1
    WHERE Parent_ID IN (:old.Parent_ID, :new.Parent_ID); END;
/
/* Try to change the Child's Attr and move it to another Parent ... */
UPDATE Childs SET Attr = 1, Parent_ID = 2 WHERE Child_ID = 1;
/* ... and receive an error */

--# ORA-04094: table T.PARENTS is constraining, trigger may not modify it
--# ORA-06512: at "T.CHILDS_CHANGED", line 2
--# ORA-04088: error during execution of trigger 'T.CHILDS_CHANGED'

/* Notify active Parents on Child's attributes change.

   Now we will use the Parents table with appropriate filter (WHERE Is_Active = 1)

   instead of the Active_Parents view.
*/
CREATE OR REPLACE TRIGGER Childs_Changed BEFORE UPDATE OF Attr, Parent_Id ON Childs FOR EACH ROW
BEGIN
    UPDATE Parents
    SET Child_Modified = 1
    WHERE Parent_ID IN (:old.Parent_ID, :new.Parent_ID)      AND Is_Active = 1;
END;
/
/* Try to change the Child's Attr and move it to another Parent ... */
UPDATE Childs SET Attr = 1, Parent_ID = 2 WHERE Child_ID = 1;
/* It works now! */

SELECT * FROM Parents;

--# PARENT_ID CHILD_MODIFIED  IS_ACTIVE
--#---------- -------------- ----------
--#        1              1          1
--#        2              0          0

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 20 1998 - 09:40:04 CDT