title: Oracle trigger - from child table to parent
body: <p>I'm trying to create a trigger that would aggregate values of a child table (after insert or update) to a parent table.
  The child table <code>C</code> has fields <code>id, year, month, value</code>, and the parent <code>P</code> - <code>id, year, value</code>.
  When a new row is inserted to C or an existing one updated, the trigger should sum all month values for that year, and write the sum to the corresponding row in <code>P</code> .
  Here's how i implemented it :</p>
  
  <pre><code>CREATE OR REPLACE
  TRIGGER tr__aggregation 
  AFTER INSERT OR UPDATE ON C
  FOR each row 
  DECLARE
  
  BEGIN
  
  
   MERGE INTO P o
              USING (SELECT id, year, sum(value) as val
                    FROM C 
                     WHERE id = :NEW.id
                     AND year = :NEW.year
                     GROUP BY id, year) n 
              ON (n.id = o.id AND n.year = o.year)
              WHEN NOT MATCHED THEN INSERT (o.id, o.year, o.value) 
                                    VALUES (n.id, n.year, n.value)
              WHEN MATCHED THEN UPDATE SET o.value = NVL(n.value, o.value);
  
  EXCEPTION WHEN OTHERS THEN
      pck_util.pr_exception_info(TRUE);
      RAISE;
  END;
  </code></pre>
  
  <p>For some reason i get this error : 
  <code>SQL Error: ORA-04091: table C is mutating, trigger/function may not see it</code></p>
  
  <p>I don't understand why, i'm not trying to modifiy <code>C</code>, only <code>P</code>.
  Thank you!</p>