Cascade delete with Oracle

From: Michael Kochetkov <mkochetk_at_trustworks.commm>
Date: 2000/04/18
Message-ID: <38fc9f6a@newshost.elvis.ru>#1/1

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 Tue Apr 18 2000 - 00:00:00 CDT