Re: mutating table

I don't think you can do it only with on delete cascade and/or triggers. But you can do it with foreign key without on delete cascade for integrity constraints and a procedure to cascade the delete of a row in X.

Here's an example:

create table x (idx number primary key); create table y (idy number primary key,

                idx number constraint fk_yx references x);
create table z (idz number primary key,
                idy number constraint fk_zy references y);

create or replace procedure del_x (p_idx x.idx%type) is begin
delete from z

   where exists (select 1 from y where y.idx=p_idx and z.idy=y.idy); delete from y where y.idx=p_idx;
delete from x where x.idx=p_idx;
end;
/

SQL>select * from x;

       IDX

         0
         1

SQL>select * from y;

       IDY IDX
---------- ----------

         1          0
         2          0
         3          1
         4          1

SQL>select * from z;

       IDZ IDY
---------- ----------

         1          3
         2          3
         3          2
         4          1

SQL>exec del_x(0);

PL/SQL procedure successfully completed.

SQL>select * from x;

       IDX

         1

SQL>select * from y;

       IDY IDX
---------- ----------

         3          1
         4          1

SQL>select * from z;

       IDZ IDY
---------- ----------

         1          3
         2          3

Harald Mitterhofer a écrit dans le message <37C3AA7B.833C2DFD_at_pcb899.gud.siemens.at>...
>Hi,
>I would like to ask you for a possible solution for the following
>problem:
>I have 3 tables: X, Y, Z, where Z references Y and Y references X;
>If a row in X is deleted, all child-rows in Y and Z also have to be
>deleted; this is perfect for "on delete cascade" in Y and Z, but.....
>if a row in Y is deleted, the rows in Z must not be deleted, but the
>deletion has to fail, if child-rows exist;
>so I think I must not use "on delete cascade" in Z;
>
>how can I solve this problem? I tried to write a "before delete for each
>
>row" trigger for X, which deletes the appropriate rows in Z, but this
>doesn't work, because Z is a mutating table;
>so I tried to store the deleted id's of X in a temp-list in the
>row-trigger and delete the Z-records in a statement-trigger, but I can
>find no way to do this right;
>
>thanks a lot
>harald
>
>
>
Received on Wed Aug 25 1999 - 07:32:29 CDT