Re: Help! Error ORA-4091 on trigger.

From: William Nunn <nunnw_at_bcstec.ca.boeing.com>
Date: Thu, 23 Mar 1995 13:31:00 GMT
Message-ID: <D5wAvp.5Ep_at_bcstec.ca.boeing.com>


Vince Cross (bartok_at_bnr.ca) wrote:
: I am trying to implement an ON DELETE SET NULL rule through a trigger (since
: Oracle doesn't yet support this directly). The scenario is this: Table B
: has foreign key references to Table A. When I delete from Table A, I want to
: set the references in Table B to NULL. Here is the trigger code:
 -
: CREATE OR REPLACE TRIGGER trigger_name
: BEFORE DELETE ON table_a
: FOR EACH ROW
: BEGIN
: UPDATE table_b
: SET fld_1 = NULL, fld_2 = NULL
: WHERE fld_1 = :old.fld_1
: AND fld_2 = :old.fld_2;
: END;
If I understand, you want to update the row(s) on table_b which relate to table_a (designated by :old.fld_1 and :old.fld_2).

It may suffice to code the PRE-DELETE trigger with:

UPDATE table_b

   SET fld_1 = NULL,
       fld_2 = NULL
 WHERE fld_1 = :old.fld_1
   AND fld_2 = :old.fld_2;

you may want to code and handle exceptions.

From one who practices simplicity K.I.S.S, William Nunn Received on Thu Mar 23 1995 - 14:31:00 CET

Original text of this message