Re: SELECT in DELETE trigger

In article <4b7g35$mr2_at_mo6.rc.tudelft.nl>, ge_at_dutlru.tudelft.nl (Ge van Geldorp) wrote:

> Hello,
>
> I have a table (TABLEA) with groups of rows and a TABLEB containing the
> MAX per group of a column in TABLEA (can't use a view for TABLEB for reasons
> not important here). I try to keep TABLEB in sync with TABLEA by using
> triggers. When adding a row to TABLEA, the trigger checks if current MAX for
> the group to which the new row belongs and updates TABLEB when the new row
> is larger. This is done using a BEFORE INSERT trigger.
> When I do the same kind of thing (determine the new MAX) in a BEFORE DELETE
> trigger by issuing a SELECT MAX(col) FROM TABLEA WHERE group_cond AND
> key_is_not_key_to_be_deleted, I get an ORA4091 error: "table TABLEA is
> mutating, trigger/function may not see it".
> I'm puzzeled why Oracle allows the same kind of SELECT on an INSERT trigger
> but doesn't allow it on a DELETE trigger.

Because your BEFORE INSERT trigger takes advantage of the specific (and documented) exception to the mutating table restriction.

See the "Mutating and Constraining Tables" section of the chapter on "Using Database Triggers".

You possibly have a case for the implementation of a similar exception for singleton deletes.

> More importantly, how do I keep the
> tables in sync when I can't do a SELECT on TABLEA in the trigger?

The fact that you believe that your BEFORE INSERT trigger works implies that you have only tested singleton inserts (as a multi-row INSERT should also get ORA4091 (assuming that it is implemented as a row trigger not a statement trigger).

IF it is an acceptable restriction to only be able to insert or delete one row from one group per SQL statement, ANDIF you can think of a way to test for violation of that restriction within a statement trigger (I can't) then it should be possible to jury-rig something with an AFTER (or BEFORE, I guess) DELETE statement trigger which tests to ensure that the restriction has not been broken.

Other than that, I'm out of ideas.

graeme

--
Disclaimer: The author's opinions are his own, and not necessarily
            those of Pyramid Technology Ltd or Pyramid Technology Inc
---------------------------------------------------------------------
      -m------- Graeme Sargent               Voice: +44(0)1252 373035
    ---mmm----- Senior Database Consultant   Fax  : +44(0)1252 373135
  -----mmmmm--- Pyramid Technology Ltd.      Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------
  We have the technology.  The tricky bit is learning how to use it.