Home -> Usenet -> comp.databases.oracle -> Mutant tables in read-only Triggers

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_at_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 - 15:03:03 CET












Original text of this message