Re: trigger with ora-04091

      Mutating Triggers

      Mutating Trigger Demo

      t_trigger causes an insert into changes this trigger causes an UPDATE on x which fires t_trigger which ... CREATE OR REPLACE TRIGGER t_mutating

      AFTER INSERT
      ON changes

      BEGIN
         UPDATE t2
         SET x = 3;

      END t_mutating;


      /

      Mutating Trigger Fix With Autonomous Transaction CREATE TABLE t1 (x 
int);
      CREATE TABLE t2 (x int);

      INSERT INTO t1 VALUES (1);

      SELECT * FROM t1;
      SELECT * FROM t2;

      CREATE OR REPLACE TRIGGER t_trigger
      AFTER INSERT
      ON t1
      FOR EACH ROW

      DECLARE

      i PLS_INTEGER;

      BEGIN
        SELECT COUNT(*)
        INTO i
        FROM t1;

        INSERT INTO t2
        VALUES
        (i);
      END;


      /


      INSERT INTO t1 VALUES (1);

      SELECT COUNT(*) FROM t1;
      SELECT COUNT(*) FROM t2;

      CREATE OR REPLACE TRIGGER t_trigger
      AFTER INSERT ON t1 FOR EACH ROW

      DECLARE

      PRAGMA AUTONOMOUS_TRANSACTION;
      i PLS_INTEGER;

      BEGIN
        SELECT COUNT(*)
        INTO i
        FROM t1;

        INSERT INTO t2
        VALUES
        (i);
        COMMIT;
      END;


      /


      INSERT INTO t1 VALUES (1);

      SELECT COUNT(*) FROM t1;
      SELECT COUNT(*) FROM t1;