Re: Cascade delete with Oracle

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.