Re: how to avoid mutating table error in triggers

Suggestion 1: Try having the trigger call a procedure. Put the update code from the trigger in a procedure.
Suggestion 2: The mutating table error is a row-level trigger. The solution is to use a statement level trigger and a row level trigger. Use a PL/SQL table inside package to record the update value to use in the row level trigger.

I hope this helps.

RWB "Magaliff, Bill" <Bill.Magaliff_at_lendware.com>@fatcity.com on 11/11/2002 09:03:47 AM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Reginald.W.Bailey_at_jpmorgan.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).