-- ************************************************************
-- *** Feel free to use this SQL-code, but please           ***
-- *** do not remove these copyright lines.                 ***
-- *** (C) Roger Felix - Lund - Sweden                      ***
-- *** [roger_felix@hotmail.com, web_master@work.utfors.se] ***
-- *** URL: http://hem.fyristorg.com/publish/index.html     ***
-- ************************************************************

  -- #######################################
  -- Avoiding mutating table problem by 
  -- deferring the action to statement level.
  -- #######################################
  -- **************************************
  -- *** This code demonstates the use  ***
  -- *** of package temporary tables to ***
  -- *** avoid mutating triggers.       ***
  -- ***                                ***
  -- *** DESCRIPTION:                   ***
  -- *** -----------                    *** 
  -- *** Execution of row-level trigger ***
  -- *** statements is moved from the   ***
  -- *** row trigger to the statement   ***
  -- *** trigger.                       ***
  -- ***                                ***
  -- *** PLEASE NOTE:                   ***
  -- *** -----------                    *** 
  -- *** This is not always possible.   ***
  -- ************************************** 

CREATE table MY_TABLE (
   col1     varchar2(10),
   col2     number(10,5),
   col3     date);

CREATE OR REPLACE
PACKAGE MY_TABLE_TRICK AS
     TYPE t_Col1 IS TABLE OF my_table.col1%type
     INDEX BY BINARY_INTEGER;
     TYPE t_Col2 IS TABLE OF my_table.col2%type
     INDEX BY BINARY_INTEGER;
     TYPE t_Col3 IS TABLE OF my_table.col3%type
     INDEX BY BINARY_INTEGER;
  -- . . . 
     Col1 t_Col1;
     Col2 t_Col2;
     Col3 t_Col3;
  -- . . . 
     n_Entries		binary_integer := 0;
END;
/

CREATE OR REPLACE TRIGGER MY_TABLE_ROW_TRICK_TRIGGER
    BEFORE INSERT OR UPDATE
        ON MY_TABLE
            REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
BEGIN

    MY_TABLE_TRICK.n_Entries := MY_TABLE_TRICK.n_Entries + 1;
    MY_TABLE_TRICK.Col1(MY_TABLE_TRICK.n_Entries) := :new.Col1;
    MY_TABLE_TRICK.Col2(MY_TABLE_TRICK.n_Entries) := :new.Col2;
    MY_TABLE_TRICK.Col3(MY_TABLE_TRICK.n_Entries) := :new.Col3;
 -- . . . 
END;
/


CREATE OR REPLACE TRIGGER MY_TABLE_STMT_TRIGGER
    AFTER INSERT  OR UPDATE
        ON MY_TABLE
            REFERENCING NEW AS NEW OLD AS OLD
DECLARE
  v_Col1		MY_TABLE.Col1%type;
  v_Col2		MY_TABLE.Col1%type;
  v_Col3		MY_TABLE.Col1%type;
  -- . . . 
BEGIN

    FOR i IN 1..MY_TABLE_TRICK.n_Entries LOOP
      v_Col1 := MY_TABLE_TRICK.Col1(i);
      v_Col2 := MY_TABLE_TRICK.Col1(i);
      v_Col3 := MY_TABLE_TRICK.Col1(i);
      -- . . . 
      -- *********************************************
      -- *** Executed once for each inserted or    ***
      -- *** updated row                           ***
      -- *********************************************        
    END LOOP;
END;
/