Oracle trigger after insert or delete

What you’ve run into is the classic “mutating table” exception. In a ROW trigger Oracle does not allow you to run a query against the table which the trigger is defined on – so it’s the SELECT against TABLE1 in the DELETING part of the trigger that’s causing this issue.

There are a couple of ways to work around this. Perhaps the best in this situation is to use a compound trigger, which would look something like:

CREATE OR REPLACE TRIGGER TABLE1_NUM_TRG
  FOR INSERT OR DELETE ON TABLE1
COMPOUND TRIGGER
  TYPE NUMBER_TABLE IS TABLE OF NUMBER;
  tblTABLE2_IDS  NUMBER_TABLE;

  BEFORE STATEMENT IS
  BEGIN
    tblTABLE2_IDS := NUMBER_TABLE();
  END BEFORE STATEMENT;

  AFTER EACH ROW IS
  BEGIN
    IF INSERTING THEN
      UPDATE TABLE2 t2
        SET    t2.TABLE2NUM = :new.NUM
        WHERE  t2.ID = :new.TABLE2_ID;
    ELSIF DELETING THEN
      tblTABLE2_IDS.EXTEND;
      tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.TABLE2_ID;
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    IF tblTABLE2_IDS.COUNT > 0 THEN
      FOR i IN tblTABLE2_IDS.FIRST..tblTABLE2_IDS.LAST LOOP
        UPDATE TABLE2 t2
          SET t2.TABLE2NUM = (SELECT NUM
                                FROM (SELECT t1.NUM
                                        FROM TABLE1 t1
                                        WHERE t1.TABLE2_ID = tblTABLE2_IDS(i) 
                                        ORDER BY modification_date DESC)
                                WHERE ROWNUM = 1)
          WHERE t2.ID = tblTABLE2_IDS(i);
      END LOOP;
    END IF;
  END AFTER STATEMENT;
END TABLE1_NUM_TRG;

A compound trigger allows each timing point (BEFORE STATEMENT, BEFORE ROW, AFTER ROW, and AFTER STATEMENT) to be handled. Note that the timing points are always invoked in the order given. When an appropriate SQL statement (i.e. INSERT INTO TABLE1 or DELETE FROM TABLE1) is executed and this trigger is fired the first timing point to be invoked will be BEFORE STATEMENT, and the code in the BEFORE STATEMENT handler will allocate a PL/SQL table to hold a bunch of numbers. In this case the numbers to be stored in the PL/SQL table will be the TABLE2_ID values from TABLE1. (A PL/SQL table is used instead of, for example, an array because a table can hold a varying number of values, while if we used an array we’d have to know in advance how many numbers we would need to store. We can’t know in advance how many rows will be affected by a particular statement, so we use a PL/SQL table).

When the AFTER EACH ROW timing point is reached and we find that the statement being processed is an INSERT, the trigger just goes ahead and performs the necessary UPDATE to TABLE2 as this won’t cause a problem. However, if a DELETE is being performed the trigger saves the TABLE1.TABLE2_ID into the PL/SQL table allocated earlier. When the AFTER STATEMENT timing point is finally reached, the PL/SQL table allocated earlier is iterated through, and for each TABLE2_ID found the appropriate update is performed.

Documentation here.