Exception not caught in trigger

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Wed, 16 Jun 2004 19:11:56 +0200
Message-ID: <lgu0xbl95v.fsf@chi.biosolveit.local>

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

--

• EXAMPLE to demonstrate the behaviour.
  --
  --
  CREATE TABLE XXX ( ID NUMBER NOT NULL,
  --
  CONSTRAINT PK_XXX PRIMARY KEY (ID) USING INDEX );

CREATE TABLE CCC (
    ID NUMBER NOT NULL, -- references XXX.ID     NAME VARCHAR2(64) NOT NULL,
--

    CONSTRAINT PK_CCC PRIMARY KEY (ID,NAME)        USING INDEX ,
    CONSTRAINT AK_CCC UNIQUE (NAME)
       USING INDEX ,
--

    CONSTRAINT REF_CCC_2_XXX

       FOREIGN KEY (ID)
       REFERENCES  XXX(ID) 
       -- implicit ON DELETE RESTRICT

);

-
CREATE OR REPLACE TRIGGER
        adr_CCC
AFTER DELETE
ON CCC
FOR EACH ROW
DECLARE
   child_record_found EXCEPTION;
   PRAGMA EXCEPTION_INIT(child_record_found,-2292); BEGIN
    BEGIN

        DELETE FROM XXX  WHERE ID = :OLD.ID ;
	-- delete fails if other CCC reference this XXX
	-- ORA-02292: integrity constraint (MY.REF_CCC_2_XXX) violated - child record found
    EXCEPTION
	WHEN child_record_found THEN
	  NULL;

    END;
END;
/
show errors

• create one record in XXX which is referenced by one in CCC.
• then delete one in CCC. insert into XXX(ID) VALUES(1); insert into CCC(ID,NAME) VALUES(1,'A'); delete from CCC where name = 'A';
• works, no error message select * from XXX;
• create one record in XXX which is referenced by two in CCC.
• try to delete one in CCC, which fails. insert into XXX(ID) VALUES(1); insert into CCC(ID,NAME) VALUES(1,'A'); insert into CCC(ID,NAME) VALUES(1,'B');

delete from CCC where name = 'B';
*
ERROR at line 1:
ORA-02292: integrity constraint \
 (FLEXX_TEST.REF_CCC_2_XXX) violated - child record found
--

• reset example with 'truncate table CCC'

Thank you,

Volker

--

Volker Apelt Received on Wed Jun 16 2004 - 12:11:56 CDT