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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
