Mutant tables in read-only Triggers

From: Marcos Barroso <selecaopessoal_at_hotmail.com>
Date: 21 Nov 2001 06:03:03 -0800
Message-ID: <93a03b96.0111210603.580ab514@posting.google.com>

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;

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 Received on Wed Nov 21 2001 - 08:03:03 CST