Re: mutating tables
Date: Wed, 25 Aug 1999 14:34:02 +0200
Message-ID: <7q0o69$eih$2_at_oceanite.cybercable.fr>
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
<37C3AA59.64C347B5_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 - 14:34:02 CEST