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.

"Michael Kochetkov" <mkochetk_at_trustworks.commm> wrote in message news:38fc9f6a_at_newshost.elvis.ru...
> 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
>
> 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.
>
>
>
Received on Wed Apr 19 2000 - 00:00:00 CDT