Re: Question: Mutation in Statement Triggers

> jwingram_at_whale.st.usm.edu (Jonathan Wayne Ingram) writes:
> Salaam Yitbarek (bi495_at_FreeNet.Carleton.CA) wrote:
> : Table 'b' has a statement trigger 't' associated with
> : it that does a query on 'b'. When there is an insert,
> : delete or update on 'b', the trigger executes properly.
> : Table 'b' has a parent table 'a'. The reference from
> : 'b' to 'a' is set to ON DELETE CASCADE. When a row
> : in 'a' is deleted, and Oracle runs off to delete the
> : corresponding rows in 'a', it returns an error saying
> : that 'b' is mutating, and thus it cannot execute
> : the trigger 't'. Any idea why?
> Yes, because not only is table a being altered, but the index
> on table b is being altered because a has been changed.
> Solution: 1) Remove your foreign key, replacing it
> with another after statement trigger that has
> similar functionality. 2) Beats the heck out of me.

You can avoid the locking that occurs from a foreign key relationship by placing an index on the foreign key column. It is not indexed by default.

> My personal opinion is that Oracle took the cheap way out
> of the triggers -- I know the row has just been inserted,
> but it was ME that just inserted it, so why can't I read
> the table?
>>>>

Imagine how bad the code would be that people wrote if they were allowed to get away with this kind of shenanigans? It would operate terribly, and they'd blame the database anyway. I'm not adverse to doing away with a restriction or two, but it sure helps to keep people honest. Far too many people try to read a table in a trigger when they should be using the :NEW and :OLD operators -- I'd hate to see what things would be like if they were provided with more stupid ways to code. :)

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Wed May 31 1995 - 00:00:00 CEST