Help! Error ORA-4091 on trigger.

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.

The info in oerr and the codes manual is:

04091, 00000, "table %s.%s is mutating, trigger/function may not see it"

// *Cause: A trigger (or a user defined plsql function that is referenced in
//         this statement) attempted to look at (or modify) a table that was
//         in the middle of being modified by the statement which fired it.
// *Action: Rewrite the trigger (or function) so it does not read that table.

How am I supposed to do this? Page 7-5 of the Concepts manual recomends the use of a trigger for this kind of integrity rule, but does not supply an example. BTW, this is on version 7.1.4.1.0 on HP/UX 9.xx.

Thanks,
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