From: omaathuis@keyaccess.nl   
      
   Simply placing the select statement in a statement level trigger instead in   
   a row level trigger gives the solution. In addition, you need to store the   
   old en new values in something like a packaged pl/sql table, which you can   
   acces in the statement level trigger. Filling the pl/sql table occurs in the   
   row level trigger. Besides, the query is a little bit useless when   
   inserting, since in that case old values simply don't exists, i.e. equals   
   null (numofdocs whill alwasy equal 0 when inserting).   
   "M Mueller"  schrieb im Newsbeitrag   
   news:ac8fbcca.0402240557.55d69a39@posting.google.com...   
   > Hello -   
   >   
   > I'm using Oracle 8i and am running into the infamous mutating table   
   > error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger.  I have seen   
   > examples for getting around mutating table errors if you either need   
   > access to the :old or the :new values within a trigger.  But what if   
   > you need access to both?  Or is this even possible (I'm fairly new to   
   > triggers).   
   >   
   > I have to execute a select count(*) from trigger_table where   
   > value1=:old.value1 and value2=:old.value2.  Based on this result, log   
   > files must be written utilizing the :new values. A simplified example   
   > of my trigger is below:   
   >   
   > CREATE OR REPLACE TRIGGER mut_trigger   
   > AFTER INSERT OR UPDATE OF value1, value2   
   > ON table_a   
   > REFERENCING OLD AS OLD NEW AS NEW   
   > FOR EACH ROW   
   > DECLARE   
   > numofdocs number;   
   > BEGIN   
   > select count(*) into numofdocs from table_a where value1=:old.value1   
   > and value2=:old.value2;   
   > if (numofdocs > 0) then   
   >   insert into log_table1 values ('Log data', :new.value1,   
   > :new.value2);   
   >   insert into log_table2 values ('Log data', :new.value1,   
   > :new.value2);   
   > end if;   
   > END;   
   >   
   > Is there any way to get what I need using one trigger?   
   >   
   > Thanks so much,   
   > Melissa   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)