Re: Table mutating! - what the hell does that mean?

In a trigger text, you cannot read from, insert or update the same table or any other child tables related to that table which the trigger is defined on otherwise the trigger is said to be 'mutating'.

There have been a few threads on this newsgroup recently (2-3 weeks) about getting around the problem (e.g moving the trigger from a row level to statement level and using pl/sql tables etc).

Try using dejanews www.dejanews.com and search on 'mutating' in this newsgroup. I'm positive that quite a few solutions will pop up.

Mark

Adrian Harrison wrote in message <3701e9a3.80399408_at_news.globalnet.co.uk>...
>Using Oracle 7.3 , NT4 (sp4), VB6, ODBC
>
>I'm getting an error with an After Insert trigger which has got me
completely baffled -
>
>Error message -
>///////////////////////////////////////////////////////////////////////////
///////////
>[Microsoft][ODBC driver for Oracle][Oracle]ORA-04091: table NVZ.NVZ_FARMS
is mutating,
>trigger/function may not see it
>ORA-06512: at "NVZ.NVZ_FARMS_AFTER_INSERT", line 2
>ORA-04088: error during execution of trigger 'NVZ.NVZ_FARMS_AFTER_INSERT'
>///////////////////////////////////////////////////////////////////////////
///////////
>
>nvz_visits.farm_id is the foreign key in to the nvz_farms table - basically
after a new farm is
>created in nvz_farms I need a blank record created in nvz_visits table.
farm_id is generated via a
>before insert/sequence combination in the nvz_farms table. This is a
one-to-one relationship!
>
>After Insert trigger
>///////////////////////////////////////////////////////////////////////////
///////////
>CREATE OR REPLACE TRIGGER nvz_farms_after_insert
>AFTER INSERT ON nvz_farms FOR EACH ROW
>BEGIN
> INSERT INTO nvz_visits (farm_id) VALUES (:new.farm_id);
>END nvz_farms_after_insert;
>/
>show errors;
>///////////////////////////////////////////////////////////////////////////
///////////
>
>By the way the user that created the trigger is "nvz" and is the same user
that trying to insert a
>new farm!
>
>I don't understand what mutating means?
>
>Any ideas
>
>Thanks
>
>Adrian Harrison
>
Received on Wed Mar 31 1999 - 05:23:23 CST