Re: Cascade delete with Oracle

Thank you for reply. It is very purposeful.

[...]
>
> The way to do this in Oracle is simply:
>
> alter table B add constraint b_fk_to_a foreign key (fk_col) references a
> (pk_col) ON DELETE CASCADE;
>
> and you are done.

But I should not be able to delete the last B for A. I.e. I cannot delete from B where AId = <number>.

>
> If you insist on doing it in triggers (and you really should not) see
I use ErWin (phisical model is rather large and I support several DBMS) and it has a lot of restrictions (the lack of references for example). And it looks like the triggers are the only way... But anyway if you do think that I can achieve the functionality "cannot delete the last B for A" (I have given the MS SQL code for the purpose not to be misunderstood) please give me another hint. I am a newbie with Oracle and if your foreign key constraint mentioned above is the thing I need (I do not see that it is :( ) I will be sorry for my stupidity.

> http://osi.oracle.com/~tkyte/Mutate/index.html
It looks like to be the right point, but it is too complex for me and I am stuck to ErWin and I hardly believe that it supports packages... And the main doubt: will it be able to support several deletes from different connections?

With regards,
Michael Kochetkov.

>
>
>
> > I should have cascade delete for A;
> > I cannot delete the last B record for A, i.e. A-->B one-to-many, and I
> > should be able to delete all the B if corresponding A is deleted.
> >
> > Please, consider the statement:
> >
> > delete from A where AId < 5
> >
> > I have the following for MS SQL:
> >
> > create trigger tD_A on A for DELETE as
> > begin
> > delete B
> > from B,deleted
> > where
> > B.BId = deleted.AId
> > end
> >
> > create trigger tD_B on B for DELETE as
> > begin
> > declare @errno int,
> > @errmsg varchar(255)
> > begin
> > if exists (select deleted.AId from deleted, A
> > where deleted.AId = A.AId and
> > deleted.AId not in (select AId from B)
> > )
> > begin
> > select @errno = 44444,
> > @errmsg = 'Cannot delete last B for A.'
> > goto error
> > end
> > ...
> > return
> > error:
> > ...
> > end
> >
> > Then I try this for Oracle:
> >
> > create trigger tD_A after DELETE on A for each row
> > begin
> > delete from B
> > where
> > B.AId = :old.AId;
> > ...
> > end;
> >
> > create trigger tD_B AFTER DELETE on B for each row
> > declare numrows INTEGER;
> > begin
> >
> > select count(*) into numrows
> > from A -- !!!!!!!!!!!!!! ORA-04091: Table A is mutating error
> > where :old.AId = A.AId and
> > :old.AId not in (select AId from B);
> > if numrows > 0 then
> > raise_application_error(44444,
> > 'Can not delete last B for A.');
> > end if;
> > ...
> > end;
> >
> > I do not know how I can realize constraints I have mentioned above
 with
> > Oracle. There is no deleted for the table scope there. And for each
 row is
> > bad.
> >
> > I would appreciate any help or suggestions.
> >
> > With regards,
> > Michael Kochetkov.
> >
> >
>
> --
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries
> http://osi.oracle.com/~tkyte/index.html
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Apr 18 2000 - 00:00:00 CDT