Re: Cascade delete with Oracle

Sorry, A-->B non-identifying relationship!!!!

Table A: AId -- primary key (PK)
Table B: BId -- PK

               AId -- foreign key (FK) from A

Michael Kochetkov.

"Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message news:8di9dv$5j1$1_at_nnrp1.deja.com...
> In article <38fc9f6a_at_newshost.elvis.ru>,
> "Michael Kochetkov" <mkochetk_at_trustworks.commm> wrote:
> > Hi all,
> > I have the problem with Oracle cascade delete.
> > I have the following:
> > Table A: AId -- primary key (PK)
> > Table B: AId -- PK and foreign key (FK) from A
> >
>
> 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.
>
>
> If you insist on doing it in triggers (and you really should not) see
> http://osi.oracle.com/~tkyte/Mutate/index.html
>
>
>
> > 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 Wed Apr 19 2000 - 00:00:00 CDT