Compound trigger for mutating table

Compound trigger for mutating table

For some this is sliced cookie ;-), but I hadn't encountered mutating table for a long time and was still set on creating a package and three triggers (before update/insert, before update/insert for each row and after update/insert) to collect in a pl/sql table the changes to perform some kind of action ... so something like this (yeah the code is not very useful, it was just for testing purposes)

Old style

create table xxx_compound_test (nr number, action_date date);
insert into xxx_compound_test values (1,null);
insert into xxx_compound_test values (2,null);
insert into xxx_compound_test values (3,null);
create or replace package xxx_compound_test_pkg
as
  type
    r_mail_test is record
    (
      nr        number
    , recipient varchar2(240)
    , message   varchar2(240)
    );
   
  type
    t_mail_test is table of r_mail_test index by binary_integer;
   
  g_mail_test t_mail_test;
  g_nr_of_values number;
  procedure init;
  procedure add_value (added_value r_mail_test);
end;
/
create or replace package body xxx_compound_test_pkg
as

  procedure init
  is
  begin
    g_mail_test.delete;
    g_nr_of_values := 0;
  end;
 
  procedure add_value (added_value r_mail_test)
  is
  begin
    g_nr_of_values := g_nr_of_values + 1;
    dbms_output.put_line ('Adding value ' || g_nr_of_values || ' for nr ' || added_value.nr);
    g_mail_test (g_nr_of_values).nr := added_value.nr;
    g_mail_test (g_nr_of_values).recipient := added_value.recipient;
    g_mail_test (g_nr_of_values).message := added_value.message;
  end;
end;
/

create or replace trigger xxx_compound_test_bu before update on xxx_compound_test
begin
  xxx_compound_test_pkg.init;
end;
/
create or replace trigger xxx_compound_test_bru before update on xxx_compound_test
for each row
declare
  l_added_value xxx_compound_test_pkg.r_mail_test;
 
begin
  l_added_value.recipient := 'Test ' || :new.nr;
  l_added_value.message   := 'Test message';
  l_added_value.nr        := :new.nr;
 
  xxx_compound_test_pkg.add_value (l_added_value);
end;
/
create or replace trigger xxx_compound_test_au after update on xxx_compound_test
begin
  -- do your mail thingy
  dbms_output.put_line ('Nr of records: ' || xxx_compound_test_pkg.g_nr_of_values);
 
  <<mails>>
  for i in xxx_compound_test_pkg.g_mail_test.first .. xxx_compound_test_pkg.g_mail_test.last
  loop
    dbms_output.put_line ('Sending mail to ' || xxx_compound_test_pkg.g_mail_test (i).recipient);  
  end loop mails;
 
end;
/

New style

But a compound trigger is much simpler since you only need one trigger and no package to maintain ..

create or replace trigger xxx_mail_test_comp for update of action_date on xxx_compound_test
compound trigger
  type
    r_mail_test is record
    (
      nr        number
    , recipient varchar2(240)
    , message   varchar2(240)
    );
   
  type
    t_mail_test is table of r_mail_test index by binary_integer;
   
  g_mail_test t_mail_test;
  g_nr_of_values number;
  l_added_value r_mail_test;
  procedure init
  is
  begin
    g_mail_test.delete;
    g_nr_of_values := 0;
  end init;
 
  procedure add_value (added_value r_mail_test)
  is
  begin
    g_nr_of_values := g_nr_of_values + 1;
    dbms_output.put_line ('Adding value ' || g_nr_of_values || ' for nr ' || added_value.nr);
    g_mail_test (g_nr_of_values).nr := added_value.nr;
    g_mail_test (g_nr_of_values).recipient := added_value.recipient;
    g_mail_test (g_nr_of_values).message := added_value.message;
  end add_value;
  
  BEFORE STATEMENT IS
  BEGIN
    init;
  END BEFORE STATEMENT;
 
  AFTER EACH ROW IS
  BEGIN
    l_added_value.recipient := 'Test ' || :new.nr;
    l_added_value.message   := 'Test message';
    l_added_value.nr        := :new.nr;
 
    add_value (l_added_value);
  END AFTER EACH ROW;
 
  AFTER STATEMENT IS
  BEGIN
    dbms_output.put_line ('Nr of records: ' || g_nr_of_values);
   
    <<mails>>
    for i in g_mail_test.first .. g_mail_test.last
    loop
      dbms_output.put_line ('Sending mail to ' || g_mail_test (i).recipient);  
    end loop mails;
  END AFTER STATEMENT;
END;
/