Re: Exception not caught in trigger

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Thu, 17 Jun 2004 12:13:24 +0200
Message-ID: <lgfz8ulcfv.fsf@chi.biosolveit.local>

Daniel Morgan <damorgan_at_x.washington.edu> writes:

>> Volker Apelt wrote:
>
>>>> Oracle 92, linux
>>>> Why is the ORA-02292 'child record exists' exception not caught
>>>> in trigger adt_CCC? My schema contains two tables XXX and CCC with
>>>> a referential constraint from CCC to XXX
>>>> and an 'AFTER DELETE FOREACH ROW' trigger on CCC, which cleans up
>>>> the parent records if no child record referres to them any
>>>> longer. Inside the trigger I expect to see an ORA-02292 while other
>>>> records still reference the same record in XXX. But I thought
>>>> the exception should be caught inside the EXCEPTION block
>>>> and should never escape from the trigger. The same kind of trick
>>>> works for duplicate inserts into 'instead of' triggers and views.
>>>>
>>>> table XXX table CCC
>>>> --------- ---------
>>>> ID -|-------0< ID
>>>> NAME
>>>>
>
>>
>> One solution would be a function such as the following that
>> determines whether the delete should take place.
>>
>> CREATE OR REPLACE FUNCTION ckcnt (IDin ccc.id%TYPE) RETURN BOOLEAN IS
>> PRAGMA AUTONOMOUS_TRANSACTION;
>> i PLS_INTEGER;

Thank you Daniel,

but the AUTONOMOUS_TRANSACTION will see the state 'before' the transaction. That will help me to avoid a 'mutating table' error, but the record count seen by CKCNT is wrong, if the insert and delete operations happen within the same transaction.

The AUTONOMOUS_TRANSACTION works if there is always a commit between each INSERT and DELETE. Otherwise a mass delete will miss the delete on XXX.

I guess have to use the package+triggers solution to record deleted entries and delete them in a AFTER STATEMENT trigger.

But the main question is still there.

Why is the ORA-02292 not caught inside the AFTER DELETE trigger?

It is clearly raised by the DELETE in adt_CCC. If you remove it the error message goes away.

Or, why is DUP_VAL_ON_INDEX catchable and ORA-02292 isn't?

• example for trigger catching DUP_VAL_ON_INDEX. CREATE VIEW LIKE_CCC AS SELECT * FROM CCC;

CREATE OR REPLACE TRIGGER instinsr_CCC
INSTEAD OF INSERT ON LIKE_CCC
FOR EACH ROW
DECLARE
   c NUMBER;
begin

       insert into CCC (
         ID,
         NAME
       )
       values (
         :new.ID,
	 :new.NAME
       );
exception 
        when DUP_VAL_ON_INDEX then begin
	    null; -- ignore duplictes
       end;

end;
/

insert into like_ccc(1,'C');
insert into like_ccc(1,'C'); -- both inserts work

-- 
Volker Apelt                   

Received on Thu Jun 17 2004 - 05:13:24 CDT