Oracle trigger for updating table based on sum of triggering table

I have the following table data structure:

PARENT
ID      TOTAL_AMOUNT
101     10
102     15

CHILDREN
ID    PARENT_ID  AMOUNT
201   101        6
202   101        4
203   102        10
204   102        5

I'd like to create a trigger that updates PARENT.TOTAL_AMOUNT to be the sum of its child records when CHILDREN is modified. But I can't seem to get around the "table is mutating" restriction.

Using Oracle 11.

EDIT:

Here is code to reproduce the sample scenario above.

CREATE TABLE PARENTS (
  ID NUMBER PRIMARY KEY,
  TOTAL_AMOUNT NUMBER
);

CREATE TABLE CHILDREN (
  ID NUMBER PRIMARY KEY,
  PARENT_ID NUMBER REFERENCES PARENTS(ID),
  AMOUNT NUMBER
);

INSERT INTO PARENTS( ID, TOTAL_AMOUNT ) VALUES ( 101, 10 );
INSERT INTO PARENTS( ID, TOTAL_AMOUNT ) VALUES ( 102, 15 );

INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 201, 101, 6 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 202, 101, 4 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 203, 102, 10 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 204, 102, 5 );


CREATE OR REPLACE TRIGGER TRG_TEST
  AFTER UPDATE OF AMOUNT ON CHILDREN
  FOR EACH ROW
BEGIN

  UPDATE PARENTS
  SET TOTAL_AMOUNT = (
    SELECT SUM(AMOUNT)
    FROM CHILDREN
    WHERE PARENT_ID = :NEW.PARENT_ID
  );
END;
/

Running the following statement produces error:

UPDATE CHILDREN SET AMOUNT = 11 WHERE ID = 204;

Error starting at line : 44 in command -
UPDATE CHILDREN SET AMOUNT = 11 WHERE ID = 204
Error report -
ORA-04091: table RMS.CHILDREN is mutating, trigger/function may not see it
ORA-06512: at "RMS.TRG_TEST", line 3
ORA-04088: error during execution of trigger 'RMS.TRG_TEST'

You would be better to use a VIEW or a MATERIALIZED VIEW to generate the totals.

If you must use a trigger (don't) then you can change from using AFTER ... EACH ROW to an AFTER statement trigger:

CREATE OR REPLACE TRIGGER TRG_TEST
  AFTER UPDATE OF AMOUNT ON CHILDREN
BEGIN
  UPDATE PARENTS p
  SET TOTAL_AMOUNT = (
    SELECT SUM(AMOUNT)
    FROM   CHILDREN c
    WHERE  c.PARENT_ID = p.ID
  );
END;
/

If you want the trigger to be more efficient and only update the ids that are modified then you could use a composite trigger.

CREATE OR REPLACE TRIGGER TRG_TEST
  FOR UPDATE OF AMOUNT ON CHILDREN
COMPOUND TRIGGER
  TYPE ids_type IS TABLE OF PARENTS.ID%TYPE;
  ids ids_type := ids_type();
AFTER EACH ROW
  IS
  BEGIN
    IF :OLD.parent_id IS NOT NULL AND :OLD.parent_id NOT MEMBER OF ids THEN
      ids.EXTEND;
      ids(ids.COUNT) := :OLD.parent_id;
    END IF;

    IF :NEW.parent_id IS NOT NULL AND :NEW.parent_id NOT MEMBER OF ids THEN
      ids.EXTEND;
      ids(ids.COUNT) := :NEW.parent_id;
    END IF;
  END AFTER EACH ROW;  
AFTER STATEMENT
  IS
  BEGIN
    FORALL i IN 1 .. ids.COUNT
      UPDATE parents
      SET   total_amount = (SELECT SUM(amount)
                            FROM   children
                            WHERE  parent_id = ids(i))
      WHERE id = ids(i);
  END AFTER STATEMENT;
END;
/

fiddle