Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Mutant tables in read-only Triggers

Re: Mutant tables in read-only Triggers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 21 Nov 2001 10:31:02 -0800
Message-ID: <9tgrt602f3q@drn.newsguy.com>


In article <93a03b96.0111210603.580ab514_at_posting.google.com>, selecaopessoal_at_hotmail.com says...
>
>Hello,
>
>I am having problem with mutant tables, even if I don&#8217;t change
>anything inside the trigger. The message is "ORA-04091 table xxx is
>mutating, trigger/function may not see it"
>
>I want to use an after insert/update trigger to check this and I need
>to use the updated fields in SELECT statement. I don't want to use a
>before trigger.
>
>Let&#8217;s see an simple example:
>Suppose I want to avoid a single customer to get more then $ 500 of
>credit.
>
>create or replace trigger CredLimit
> After Insert or Update on AccountMoviment
> For each row
> declare TotalValue Integer;
> Begin
> select sum(AccountMoviment.Value) into TotalValue
> from AccountMoviment
> where :new.CustomerId = AccountMoviment.CustomerId ;
> if (TotalValue > 500) then
> Raise_Application_Error (-20000, 'Limit is $ 500');
> end if;
> End CredLimit;
>
>

Ahh, but consider what happens in GENERAL here. Say you do an update like this:

update accountMoviment

   set value = value + decode( someCode, 'A', 100, 'B', -100, 0 );  where customerId = 5;

Now, lets say you have data:

customerid         somecode              value
-------------      -----------           --------
5                  A                     250
5                  B                     250

After the update -- the data would be:

5       A          350
5       B          150 

which is OK with you but.... during the processing of the update, we will have EITHER:

5       A       350
5       B       250

or

5       A      250
5       B      150

depending on the order in which we process rows (which can change easily). We would now be in the position that in one database with the original source data, your update WORKS, in another database with the SAME EXACT DATA (just physically stored in a different order) your update does not work. Now what? It gets worse even if you were deriving other values off of this field -- you could get different answers depending on the order in which the data was processed.

Hence, you'll need to do this check AFTER the update/insert happened. See http://osi.oracle.com/~tkyte/Mutate/index.html for how to do this processing in an AFTER trigger.

Don't forget about a multi-user environment as well -- there are things to consider there. Suppose you have the data

customerid           value
-------------        --------
5                    200
5                    200

and simultaneously, 2 different sessions issue:

insert into accountmoviment values ( 5, 100 );

Each (not able to see the others work) will compute "500" since they can only see whats been committed and what they added (non-blocking reads). Now what? at the end -- you have $600 which exceeds your value. You'll need to add some serialization at the customerid level in order to avoid this.

>I also rewrite the above trigger using DECLARE CURSOR and Sub
>Transactions. The result was the same. In all cases, trigger
>compilation was OK, but not run.
>
>My questions are:
>
>As my trigger don't insert/update/delete anything, why I can't use the
>updated fields in a simple SELECT statement ?
>
>Is there a way to overcome this oracle message ?
>
>Thanks in advance
>
>Marcos Augusto

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Wed Nov 21 2001 - 12:31:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US