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

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