Compound Triggers

Question

Problem - Want to avoid the problem of mutating triggers by using the compound trigger. But unable to do so

Background - I want to insert data in new table " Tracking Table " whenever there is change in Main table "CUSTOM_ITEM"

Design is such that, everytime a row is created in table an ITEM_ID is generated but there is a column FIRST_ITEM_ID that remains same in some cases.

So whenever a new row is added, I want to check its FIRST_ITEM_ID and then check the whole table and find out all the ITEM_IDs having that same FIRST_ITEM_ID.

And I want to insert all those rows in the New table using trigger.

Is it even possible ?

Attaching the trigger :

CREATE OR REPLACE TRIGGER APP.TEST_TRG
FOR DELETE OR INSERT OR UPDATE 
ON APP.CUSTOM_ITEM
COMPOUND TRIGGER

TYPE t_change_tab IS TABLE OF APP.TEST_TRG.OBJECT_ID%TYPE;
g_change_tab t_change_tab := t_change_tab();

BEFORE EACH ROW IS
  BEGIN


      Select item_id bulk collect into g_change_tab from CUSTOM_ITEM where first_item_id =
     (Select first_item_id from CUSTOM_ITEM where item_id = :NEW.item_id);


        For i in 1 .. g_change_tab.COUNT()
            LOOP 

            g_change_tab.extend;

            END LOOP;    

  END BEFORE EACH ROW;

AFTER STATEMENT IS

    BEGIN
    For i in 1 .. g_change_tab.COUNT()
    LOOP

        app.bc_acs_pkg.populate_TEST_TRG     /* Package Inserts data */
                (p_object_type => 'ITEM',
                p_object_id => g_change_tab(i));


    END LOOP;

    g_change_tab.delete;
  END AFTER STATEMENT;


END ;
/

Answer 1

You can do what you want just not with your current approach. Let's take a step back. What is a mutating table exception (ORA-04091). It is thrown when you attempt to access the table on which the trigger fired in a row level event, that is not permitted. Just creating a compound trigger does not remove that restriction. So in your Before Row segment the statement

Select item_id 
  bulk collect into g_change_tab 
  from CUSTOM_ITEM where first_item_id =
     (Select first_item_id from CUSTOM_ITEM where item_id = :NEW.item_id);

is invalid, and results in raising ORA-04091. What you need is to just build your collection with the necessary ids. Then process them in the After statement segment.

create or replace trigger test_trg
for delete or insert or update 
on custom_item
compound trigger
  type t_change_tab is 
       table of custom_item.first_item%type; 

  g_change_tab t_change_tab := t_change_tab();

before each row is
      l_first_item_exists boolean := false;
      indx                integer; 
  begin
      indx := g_change_tab.first;  
      while not l_first_item_exists
        and indx is not null 
      loop
          l_first_item_exists := (g_change_tab(indx) = :new.first_item);
          if not l_first_item_exists
          then 
              indx := g_change_tab.next(indx);
          end if;               
      end loop; 

      if not l_first_item_exists
      then
        g_change_tab.extend;
        g_change_tab(g_change_tab.last) := :new.first_item;
      end if; 
end before each row;

after statement is
  begin
     for indx in g_change_tab.first .. g_change_tab.last
     loop 
         insert into tracking_table(item_id, first_item)
         select item_id, first_item
           from custom_item 
          where first_item = g_change_tab(indx);
     end loop;
end after statement;
end test_trg;

The issue here is the loops, always the slowest processing, and very bad in triggers. Below is an approach which avoids them totally. It does however require creating your type array at the schema level.

create or replace type custom_item_change_t is 
                   table of integer ; --custom_item.first_item%type;

create or replace trigger test_trg
for insert
on custom_item
compound trigger     
  g_change_tab custom_item_change_t := custom_item_change_t();

before each row is
  begin
    g_change_tab.extend;
    g_change_tab(g_change_tab.last) := :new.first_item; 
end before each row;

after statement is
  begin
   insert into tracking_table(item_id, first_item)
     select item_id, first_item
       from custom_item ci
      where first_item in (select distinct column_value
                             from table(g_change_tab)
                          ) 
        and not exists 
            ( select null 
                from tracking_table tt
               where ci.item_id = tt.item_id
                 and ci.first_item = tt.first_item
            ); 
end after statement;
end test_trg;