Home -> Usenet -> comp.databases.oracle -> [Q] Delete when last reference vanishes (trigger mutating table)

[Q] Delete when last reference vanishes (trigger mutating table)

From: Almut Herzog <al-her_at_sectra.se>
Date: 1996/11/07
Message-ID: <3281FFC7.26B5_at_sectra.se>#1/1 







Hi,



I hope someone can help with this referential integrity stuff:
I want a document to go away when its last reader goes away. I thought I
could do a smooth trigger. But alas, that results in mutating table
messages.



I really need a database solution as readers can be deleted via "on
delete cascade" by their parents.




create table document (doc_id number(5) primary key,

                       doc_spec varchar2(32));
create table reader (read_id number(5) primary key,
                     read_doc_id number(5) not null 
                        references document(doc_id));





This trigger does NOT work:



create or replace trigger reader_after_delete
after delete on reader


for each row


declare


reader_count number(3) := 0;


begin


  select count(*) into reader_count from reader
  where read_doc_id = :old.read_doc_id;


  if (reader_count = 0) then


     delete from document where doc_id = :old.read_doc_id;
  end if;


end;


/



Please reply by e-mail, too. It's faster and more reliable.
TIA!



-- 
Almut Herzog
SECTRA-Imtec AB                     e-mail: al-her_at_sectra.se
SWEDEN


Received on Thu Nov 07 1996 - 00:00:00 CET












Original text of this message