Re: Calling a proc in a trigger

From: Lazar Ivancevic <lai_at_propack-data.com>
Date: Mon, 09 Jul 2001 11:23:00 +0200
Message-ID: <3B4977F4.3F1EEABB@propack-data.com>

Matthieu DEGLIN wrote:

> Hi,
>
> I'm working on a table TABLE_TOP that has 2 columns: TOP, CODE.
> I've defined a trigger on it as followed:
>
> TRIGGER TRG_TOP
> AFTER UPDATE OF TOP ON TABLE_TOP
> FOR EACH ROW
> BEGIN
> PROC_TOP(:NEW.CODE);
> END;
>
> The procedure that is called in the trigger need to access to the table
> TABLE_TOP:
>
> PROCEDURE proc_top(p_code in char) is
> ...
> select count(*)
> into nb
> from TABLE_TOP
> where TOP = 'O'
> and CODE= p_code;
> ...
> END;
>
> BUT...
>
> The following error has occurred:
> ORA-04091: table TABLE_TOP is mutating, trigger/function may not see it
>
> How can I solve that error ?
> Is it impossible to access to a table in a trigger on that same table ?
>
> Thanks for help.

Use a temporary PL/SQL Table to store Information of modified/Inserted Record and process the information after the changes are committed.

• RESETTING counter before

CREATE OR REPLACE
TRIGGER tabletop_bef_IUD_nfer_trigger

     BEFORE INSERT OR UPDATE OR DELETE ON tabletop     BEGIN
        proc_tabletop.clear_count;
    END;
/

• ADD Entry for processing CREATE OR REPLACE TRIGGER TRG_TOP AFTER UPDATE OF TOP ON TABLE_TOP FOR EACH ROW BEGIN
  • Unique Key table? PROC_tabletop.ADD_ENTRY(:NEW.CODE); END;
• CHECKING after

CREATE OR REPLACE
TRIGGER tabletop_aft_IUD_nfer_trigger

    AFTER INSERT OR UPDATE OR DELETE ON tabletop     BEGIN
        proc_tabletop.count_top;
    END;
/

--PROCEDURE CREATE OR REPLACE
PACKAGE proc_tabletop IS

   PROCEDURE clear_count;
   PROCEDURE add_entry( ;

                        p_code      IN table_top.code%TYPE
                        );
   PROCEDURE count_top (
                        p_code      IN table_top.code%TYPE
                        );

END proc_tabletop;
/

CREATE OR REPLACE
PACKAGE BODY proc_tabletop IS

   TYPE tabletopRecTyp IS RECORD (

                tp_code proc_tabletop.code%TYPE,
                  );

   TYPE tab_tabletop_type IS TABLE OF tabletopTyp INDEX BY BINARY_INTEGER;
   tp_table     tab_tabletop_type;

   tp_tab_index BINARY_INTEGER;

   PROCEDURE clear_count IS
   BEGIN
       tp_tab_index := 0;
   END clear_count;

   PROCEDURE add_entry (

                        p_code      IN table_top.code%TYPE
                        ) IS
   BEGIN
       tp_tab_index := tp_tab_index + 1;
       tp_table(tp_tab_index).cod     := p_code;

   END add_entry ;

   PROCEDURE count_top IS

       tmp_tp_table     tab_tp_type;
       tmp_tp_tab_index BINARY_INTEGER;
   BEGIN
       tmp_tp_tab_index := tp_tab_index;
       FOR I IN 1..tmp_tp_tab_index LOOP
         tmp_tp_table(I) := tp_table(I);
       END LOOP;
       FOR I IN 1..tmp_tp_tab_index LOOP
          tp_code := tmp_tp_table(I).tp;
          -- count what you want
          -- update your record
       END LOOP;

   END count_top;
END proc_tabletop;
/

--
Lazar Ivancevic

Received on Mon Jul 09 2001 - 04:23:00 CDT