Re: Help! Error ORA-4091 on trigger.

In article <3kqbp0$suc_at_crchha60.bnr.ca>, 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;
>
>When I delete from Table A, I get the following error:
>
> ORA-04091: table TABLE_A is mutating, trigger/function may not see it.
>

Thanks to everyone who replied with suggestions. The solution is to disable the FK constraint on the child table. Unfortunately, DDL statements cannot be done in PL/SQL blocks (unless someone knows of a trick or package out there), so I have to leave the constraint disabled and enforce it with a trigger on the child table. The reason you get the above mutating error is that the parent table is undergoing a change that calls the BEFORE trigger, which tries to change a child table. But the child table cannot validate the FK reference because the parent table is changing, therefore, mutating table error. For more information on using triggers to enforce complex constraints, look at Chapter 8 of the Application Developer's Guide.

Vince

--
* disclaimer - My views respresent NT/BNR in every way, NOT!
* Please direct non-business email to VLCross_at_aol.com  (No, I'm not an idiot
         just because I have an aol account. Excessive drinking makes me one.)
* Work related stuff can go to bartok_at_bnr.ca