Re: HELP: Mutating Tables

This is what I do to get around the mutating table problem. Statement triggers do not raise this error. I store all the information in a PL/SQL table using the row triggers. Then process that information using a statement trigger. If you want, I can send you some sample code, let me know. Thanks,
  Kittu.

Alistair mackay (eddie_at_tag.co.uk) wrote:
: I'm trying to code a DELETE SET NULL trigger (note in this instance it
: has to be DELETE and not DELETE OR UPDATE)
 

: Consider
 

: CREATE TRIGGER detail_set_null AFTER DELETE ON master
: REFERENCING OLD AS deleted
: FOR EACH ROW
: BEGIN
: UPDATE detail SET
: detail.foreignkey = NULL
: WHERE detail.foreignkey = :deleted.primarykey;
: END
 
: If I create the trigger as AFTER DELETE, I get an RI error from Oracle
: telling me that dependant detail rows exist.
: If I create the trigger as BEFORE DELETE, I get a mutating table
: exception on master.
 

: How do I get round it?
 

: Replies by email please.
 

: TIA.

: --
: ------------------------------------------------------------------------
: | A. Mackay EMail eddie_at_tag.co.uk |
: | http://www.tag.co.uk/tag/ |
: | Technology Applications Group |
: | |
: | Comments are my own and do not reflect the policies of the company I |
: | work for. No responsiblitly accepted for incorrect advice given. |
: ------------------------------------------------------------------------
Received on Fri Aug 02 1996 - 00:00:00 CEST