title: Oracle triggers - problem with mutating tables
body: <p>My tables:</p>
  
  <pre><code>TableA (id number, state number)
  TableB (id number, tableAId number, state number)
  TableC (id number, tableBId number, state number)
  </code></pre>
  
  <p>So items in TableC are TableB's children and items in TableB are TableA's children. Vice versa - items in TableA are TableB's parents and items in TableB are TableC's parents.</p>
  
  <p>I'd like to control state of parent items... let's say for example, that we have this data:</p>
  
  <pre><code>TableA (id, state): 
  1, 40
  
  TableB (id, tableAId, state): 
  1, 1, 40
  2, 1, 60
  
  TableC (id, tableBId, state): 
  1, 1, 40
  2, 1, 50
  3, 2, 60
  4, 2, 70
  </code></pre>
  
  <p>Parent state should always hvae the smallest state of his children. So if we now update TableC like this:</p>
  
  <pre><code>update TableC set state = 50 where Id = 1;
  </code></pre>
  
  <p>my trigger should automatically update TableB (set state = 50 where id = 1) and then update also TableA (set state = 50 where id = 1)</p>
  
  <p>I'd like to do this with triggers (AFTER UPDATE, INSERT, DELETE, on TableA, TableB, TableC), so that after every action this steps would execute:</p>
  
  <ol>
  <li>get parent id</li>
  <li>find smallest state from all the children of current parent</li>
  <li>if smallest state of all children is greater than parent's state, then update parent</li>
  </ol>
  
  <p>How can I avoid 'mutating table error'? Is it save to use autonomous transactions in this example? I saw some opinions, that mutating table error indicates flaws in logic of the application - is this true and how can I change my logic in order to prevent this error?</p>
  
  <p>Thanks</p>
  
  <hr>
  
  <p>EDIT:
  Thanks for all the great answers!</p>
  
  <p>In the end, I used triggers (thanks to Vincent Malgrat, who pointed out Tom Kyte's article).</p>
  
  <hr>
  
  <p>EDIT:
  In the REAL END, I used stored procedures and removed triggers :)</p>