Tom,

I am well aware of your reservation towards triggers - but I have a something that puzzles me.

Assume that you have a table where you want to use an after statement trigger to update one or more rows in the same table as the trigger is placed on.

A lot of litterature on the internet suggests that if you collect 'the changed data' and then do your stuff in an after statement trigger then you are home free - see http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php or http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1270

<quote from last>
Trigger Restrictions on Mutating Tables

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.

The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

This restriction applies to all triggers that use the FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
</quote>

The things that puzzles me is that the statement "This restriction applies to all triggers that use the FOR EACH ROW clause.". In the following (Cut down as much as possible) is the table clearly mutating although everything is executed in the AFTER STATEMENT TRIGGER:

CREATE TABLE T
(
  PK       NUMBER                               NOT NULL,
  CONT     VARCHAR2(20 CHAR)                    NOT NULL,
  UPDDATE  DATE
)
/
CREATE OR REPLACE PACKAGE MCSDAT.t_pkg
IS
   PROCEDURE init;

   PROCEDURE COLLECT (v_rowid ROWID);

   PROCEDURE COMPLETE;
END;
/
CREATE OR REPLACE PACKAGE BODY MCSDAT.t_pkg
IS
   TYPE t_collect IS TABLE OF ROWID
      INDEX BY BINARY_INTEGER;

   v_collect      t_collect;

   PROCEDURE dbg (v_caption IN VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (v_caption || ' @ ' || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS'));
   END;

   PROCEDURE init
   IS
   BEGIN
      dbg ('Init Called');
      v_collect.DELETE;
   END;

   PROCEDURE COLLECT (v_rowid ROWID)
   IS
      l_next   NUMBER;
   BEGIN
      dbg ('Collect Called');
      l_next := v_collect.COUNT + 1;
      v_collect (l_next) := v_rowid;
   END;

   PROCEDURE COMPLETE
   IS
      l_now   DATE := SYSDATE;
   BEGIN
      dbg ('Complete Called');

      IF NOT v_inprogress
      THEN
         FORALL i IN 1 .. v_collect.LAST
            UPDATE t
               SET upddate = l_now
             WHERE ROWID = v_collect (i);
      END IF;
   END;
END;
/
CREATE OR REPLACE TRIGGER t_bs before update or insert on t
begin
   t_pkg.Init ;
end ;
/
CREATE OR REPLACE TRIGGER t_ar
   AFTER UPDATE OR INSERT
   ON t
   FOR EACH ROW
BEGIN
   t_pkg.COLLECT (:NEW.ROWID);
END;
/
CREATE OR REPLACE TRIGGER t_as
   after UPDATE OR INSERT
   ON t
BEGIN
   t_pkg.complete;
END;
/

in sqlplus:

set serveroutput on
insert into t(pk,cont) values (1,'Cont 1') ;

The result is (not surprising?):
SQL> insert into t(pk,cont) values (1,'Cont 1') ;
insert into t(pk,cont) values (1,'Cont 1')
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "MCSDAT.T_PKG", line 48
....
ORA-06512: at "MCSDAT.T_AS", line 2
ORA-04088: error during execution of trigger 'MCSDAT.T_AS'
ORA-06512: at "MCSDAT.T_PKG"

This is mutating even though the 'work' is in a AFTER STATEMENT - so where did I go wrong?

Thanks,

Lars

...
   PROCEDURE COMPLETE
   IS
      l_now   DATE := SYSDATE;
   BEGIN
      dbg ('Complete Called');

      IF NOT v_inprogress
      THEN
         FORALL i IN 1 .. v_collect.LAST
            UPDATE t
               SET upddate = l_now
             WHERE ROWID = v_collect (i);
      END IF;
   END;
.....