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

I thought I would post my reply for anyone else having this problem [If you havent you database must be first normal form ;)]

Mutating table are fun!
The problem is that any child table is considered to be "mutating" until "after the statment"
So use an after statement trigger

But this does not solve all your problems After statement triggers do NOT have meaningful access to row level values

So you will need to store the PK values so you know which row(s) to manipulate (in you case delete)

For this you take advantage of Packages and the fact that their values exist for the duration of THAT session

I use three triggers for this

a BS trigger

• initialize package variables

an AR trigger

• load PL\SQL table with primary key values For each row load a Packaged variables with PK values I have multiple overloaded versions of this prc

an AS trigger
while there are more records to process loop

        Get the next values and process accordingly end loop

Scott

Almut Herzog <al-her_at_sectra.se> wrote in article <3281FFC7.26B5_at_sectra.se>...
> 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 Fri Nov 08 1996 - 00:00:00 CST