how to avoid mutating table error in triggers

From: Magaliff, Bill <Bill.Magaliff_at_lendware.com>
Date: Mon, 11 Nov 2002 07:03:47 -0800
Message-ID: <F001.005000D7.20021111070347@fatcity.com>

Good day all,

Have the following setup -

Oracle 8.1.7.2 on solaris

parent-child realtionship between 2 tables:

table p1 has primary key pk1
table f1 has foreign key p1pk1 back to table p1.

Table p1 also has a field "haschild number(1)", used to indicate if there are ANY child records in table f1.
Any insert into table F1 sets the haschild field in the corresponding row in table P1 to 1 (true).

Trying to write an "on delete" trigger for table f1 that will set that boolean to 0 when there are now more child rows.

Came up with this:

create or replace trigger nochildtrg
after delete on f1
for each row
declare
tv_count number;
begin
select count(*) into tv_count
from f1
where p1pk1 = :old.p1pk1;
if tv_count = 0 then
update p1
set haschild = 0
where pk1 = :old.old.p1pk1;
end if;
commit;
end;
/

This plays right into the "no-no's" that produce the mutating table error on table f1 - selecting against it as part of a trigger.

Does anyone have any kind of workaround? I could implement a counter trigger, that increments a count on the parent table for each new row in the child, and decrements the count for each deleted row, but I wanted to see if there was another way.

thanks

bill
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Magaliff, Bill
  INET: Bill.Magaliff_at_lendware.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Nov 11 2002 - 09:03:47 CST