Re: Table with trigger mutation

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 7 Dec 1998 10:54:57 GMT
Message-ID: <74gc61$s71$1@news01.btx.dtag.de>

Valentine Pogrebinsky schrieb:
>
> I have two tables
>
> CREATE TABLE Obj(
> Obj_ID NUMBER(10, 0) NOT NULL PRIMARY KEY
> ) ;
>
> CREATE TABLE Org(
> Org_ID NUMBER(10, 0) NOT NULL PRIMARY KEY
> ) ;
>
> ALTER TABLE Org ADD CONSTRAINT RefObj1
> FOREIGN KEY (Org_ID)
> REFERENCES Obj(Obj_ID);
>
> When I delete from ORG record with ORG_ID, I need to delete from OBJ record
> with OBJ_ID equal ORG_ID (Right this way - detail-master). The trigger is
>
> CREATE OR REPLACE TRIGGER DELORGFROMOBJ
> AFTER DELETE ON ORG
> FOR EACH ROW
> BEGIN
> DELETE FROM OBJ
> WHERE OBJ_ID = :OLD.ORG_ID;
> END;
>
> This trigger raise exception "ORA-04091 Table ORG is mutaiting,
> trigger/function may not see it." But I really need this way modification of
> both tables
>
> Thanks for any help.
>
> Valentine Pogrebinsky

Hi,

here some words from Oracle's docs:

FOREIGN KEY identifies the column or combination of columns in the child table that makes up of the foreign key. Only use this keyword when you define a foreign key with a table
constraint clause.

REFERENCES identifies the parent table and the column or combination of columns that make up the referenced key.

If you only identify the parent table and omit the column names, the foreign key automatically references the primary key of the parent table.

The corresponding columns of the referenced key and the foreign key must match in number and datatypes.

ON DELETE CASCADE allows deletion of referenced key values in the parent table that have dependent rows in the child table and causes Oracle7 to automatically delete dependent rows
from the child table to maintain referential integrity.

If you omit this option, Oracle7 forbids deletions of referenced key values in the parent table that have dependent rows in the child table.

And here the example:

Example VII

This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:

CREATE TABLE emp

        (empno          NUMBER(4), 
        ename           VARCHAR2(10), 
        job             VARCHAR2(9), 
        mgr             NUMBER(4), 
        hiredate        DATE, 
        sal             NUMBER(7,2), 
        comm            NUMBER(7,2), 
        deptno          NUMBER(2)       CONSTRAINT fk_deptno 
                                                        REFERENCES
dept(deptno) 
                                                        ON DELETE

CASCADE ) Because of the ON DELETE CASCADE option, Oracle7 cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent
rows of the EMP table. For example, if department 20 is deleted from the DEPT table, Oracle7 deletes the department's employees from the EMP table.

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Mon Dec 07 1998 - 04:54:57 CST