title: Reading current table id during trigger operation
body: <p>I have three tables: A and B
  The relationship is A can have many B
  So B has a reference to the A.id as one of its columns</p>
  
  <pre><code>Table A
  |id|date|...
  
  Table B
  |id|A_id|...
  </code></pre>
  
  <p>I have created an Oracle trigger on table A so that when it is updated it updates an A_Mod table.</p>
  
  <p>This trigger is </p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TR_A_INSERT_UPDATE
  AFTER  INSERT OR UPDATE ON A
  FOR EACH ROW
  BEGIN
      INSERT INTO A_Mod values(..., :new.date, ...)
  END;
  </code></pre>
  
  <p>This works fine :)</p>
  
  <p>My problem si creating the trigger for table B.</p>
  
  <p>The trigger is:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TR_B_INSERT_UPDATE
       AFTER  INSERT OR UPDATE ON B
       FOR EACH ROW
       DECLARE
         ts TIMESTAMP;
  
       BEGIN
     SELECT aa.date INTO ts FROM B bb 
         INNER JOIN A aa ON a.id = bb.A_id
         WHERE bb.id = :new.id;
  
         INSERT INTO A_Mod values(..., :new.date, ...)
      END;
  </code></pre>
  
  <p>This trigger is reading the ID of the updated line in table B and then getting the date from the corresponding row in table A. It then tries to insert it into A_Mod</p>
  
  <p>The problem is I get a mutating error</p>
  
  <pre><code>Error report:
  SQL Error: ORA-04091: table B is mutating, trigger/function may not see it
  ORA-06512: at "TR_B_INSERT_UPDATE", line 5
  ORA-04088: error during execution of trigger 'TR_B_INSERT_UPDATE'
  04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
  *Cause:    A trigger (or a user defined plsql function that is referenced in
             this statement) attempted to look at (or modify) a table that was
             in the middle of being modified by the statement which fired it.
  *Action:   Rewrite the trigger (or function) so it does not read that table.
  </code></pre>
  
  <p>Looking at the <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm" rel="nofollow">docs</a> I can remove this error by removing the <code>FOR EACH ROW</code> line and having the trigger fire once per statement rather than once per row. Unfortunately I am using an ORM mapper so do not control how the updates happen. I think there would be times when the update could cover multiple rows. </p>
  
  <p>The docs say something about creating a temporary table but I am not sure how this would help. Would I have to create a temporary table inside the trigger, create a trigger on this temporary table which then updates A_Mod, update this temporary table when the trigger is fired and then delete everything after? </p>
  
  <p>Any tips greatly appreciated.</p>
  
  <p>Thanks</p>