Oracle trigger after insert or delete

Question

Sorry for my english.

I have 2 tables:

Table1
id
table2_id
num
modification_date 

and

Table2
id
table2num

I want to make a trigger which after insert or delete in Table1 updates the last value num in Table2.table1lastnum.

My trigger:

CREATE OR REPLACE TRIGGER TABLE1_NUM_TRG
  AFTER INSERT OR DELETE ON table1
  FOR EACH ROW
BEGIN
  IF INSERTING then

  UPDATE table2
  SET    table2num = :new.num
  WHERE  table2.id = :new.table2_id;

  ELSE

  UPDATE table2
  SET    table2num = (SELECT num FROM  (SELECT num FROM table1 WHERE table2_id = :old.table2_id ORDER BY modification_date DESC) WHERE ROWNUM <= 1)
  WHERE  table2.id = :old.table2_id;

  END IF;

END TABLE1_NUM_TRG; 

But after delete in Table1 I have error:

ORA-04091: table BD.TABLE1 is mutating, trigger/function may not see it
ORA-06512: at "BD.TABLE1_NUM_TRG", line 11
ORA-04088: error during execution of trigger 'BD.TABLE1_NUM_TRG'

What am I doing wrong?

Answer 1

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.