Re: Exception not caught in trigger

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 cought 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;

BEGIN
   SELECT COUNT(*)
   INTO i
   FROM ccc
   WHERE id = IDin;

   IF i < 2 THEN
     RETURN TRUE;
   ELSE
     RETURN FALSE;
   END IF;
END ckcnt;
/

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)