October 27, 2005: On using triggers to keep summary data in the nodes of hierarchical data in sync

In my last article, I demonstrated how to store hierarchical data and how to select from it. This article builds on that last article and shows how triggers can be used to keep summary data in the nodes of hierarchical data in sync.

I create almost the same three tables (item, composition and part_to_composition) as I did in On storing hierarchical data, but I add a column val in item and a column val_computed in composition:

create table item (
  id   number       primary key,
  txt  varchar2(25) not null,
  val  number       not null
);

create table composition (
  id           number       primary key,
  txt          varchar2(25) not null,
  val_computed number       not null
);

create table part_to_composition (
  id_composition references composition not null,
  id_item        references item,
  id_non_item    references composition,
  cnt            number(5)  not null check (cnt > 0), 
  check (id_item is null and id_non_item is not null or id_item is not null and id_non_item is null)
);

The column val is supposed to store the value for an item. The triggers will then calculate the sum of all items belonging to a composition and store the calculated value in val_computed.

Before I can start writing the triggers, I need a package...

create package hierarchy_pck as
  type stale_compositions is record (id number, diff number);
  type stale_compositions_t is table of stale_compositions;

  stale stale_compositions_t;

  procedure go_up_compositions(p_non_item_id in number, diff in number);
end hierarchy_pck;
/

... and the package body:

create package body hierarchy_pck as

  procedure go_up_compositions(p_non_item_id in number, diff in number) is begin

    update composition 
       set val_computed = val_computed + diff
     where id = p_non_item_id;

    for r in (select id_composition, cnt
                     from part_to_composition 
                    where id_non_item = p_non_item_id) loop

       go_up_compositions(r.id_composition,r.cnt*diff);

    end loop;
  end go_up_compositions;

  begin
    -- Initialize stale when package is loaded
    stale := stale_compositions_t();
end hierarchy_pck;
/

The use for stale and go_up_compositions will become clear in a moment.

The first trigger I create makes sure that val_computed is set to null when a row is inserted into composition.

create trigger trg_ins_composition
  before insert on composition
  for each row
begin
  :new.val_computed := 0;
end trg_ins_composition;
/

The next trigger handles updates of items. Whenever an item is updated, all compositions in which this item occured have to adjust their val_computed. The :old.val of the updated item must be substracted from val_computed while the :new.val must be added to val_computed in those compositions.

This is what the function go_up_compositions does: it takes an id of a composition (p_non_item_id) and the value to be added (diff). In deletions, diff will most probably be negative, of course.

go_up_compositions then recursively calls itself with the id_composition of compositions that contain the updated composition.

create or replace trigger trg_upd_item
  before update on item
  for each row
begin

  for r in (select id_composition, cnt
             from part_to_composition 
            where id_item in (:new.id, :old.id)) loop

    hierarchy_pck.go_up_compositions(r.id_composition, r.cnt*(:new.val-:old.val) );
  end loop;

end trg_upd_item;
/

Note, no trigger is needed for deletions of items because referential integrity makes sure that no item can be deleted which is still referenced in a part_to_composition.

The next trigger is fired on insertions of compositions. Similarly to the preceeding triggers, it employs go_up_compositions.

create or replace trigger trg_ins_part_to_composition
  before insert on part_to_composition
  for each row
declare 
  diff number;
begin

  if :new.id_item is not null then
    select :new.cnt * val          into diff from item        where id = :new.id_item;
  else
    select :new.cnt * val_computed into diff from composition where id = :new.id_non_item;
  end if;

  hierarchy_pck.go_up_compositions(:new.id_composition, diff);

end trg_ins_part_to_composition;
/

The next two triggers are a bit more complicated because I have to work around a ORA-04091 table ... is mutating, trigger/function may not see it.

This is achieved in two steps: the first trigger collects all composition.id's of rows that change along with the difference of values. This value pair (id and diff) is stored in stale. This trigger must be a row level trigger because it has to store a value pair for each row.

create trigger trg_del_upd_part_to_comp_b 
  before delete or update on part_to_composition
  for each row
declare
  diff number;
begin

  -- Assumption here: id_composition does not change

  if :old.id_item is not null then
    select - :old.cnt * val          into diff from item        where id = :old.id_item;
  else
    select - :old.cnt * val_computed into diff from composition where id = :old.id_non_item;
  end if;

  if :new.id_item is not null then
    select diff + :new.cnt * val into diff from item where id = :new.id_item;
  end if;

  if :new.id_non_item is not null then
    select diff + :new.cnt * val_computed into diff from composition where id = :new.id_non_item;
  end if;

  hierarchy_pck.stale.extend;
  hierarchy_pck.stale(hierarchy_pck.stale.count).id   := :old.id_composition;
  hierarchy_pck.stale(hierarchy_pck.stale.count).diff := diff;

end trg_del_upd_part_to_comp_b;
/

The next trigger is an after trigger. It fires when the update or deletion of the part_to_composition has completed. The trigger will then iterate over all value/pairs found in hierarchy_pck.stale and call go_up_compositions.

create trigger trg_del_upd_part_to_comp_a
  after delete or update on part_to_composition
begin

  for s in 1 .. hierarchy_pck.stale.count loop
    hierarchy_pck.go_up_compositions(hierarchy_pck.stale(s).id, hierarchy_pck.stale(s).diff);
  end loop;

  hierarchy_pck.stale := hierarchy_pck.stale_compositions_t();

end trg_del_upd_part_to_comp_a;
/

Now: filling the tables with some values:

insert into item values (1, 'item 1', 1);
insert into item values (2, 'item 2', 2);
insert into item values (3, 'item 3', 3);

insert into composition (id, txt) values (1, 'comp 1');
insert into composition (id, txt) values (2, 'comp 2');
insert into composition (id, txt) values (3, 'comp 3');

insert into part_to_composition values (1, 1, null, 2); 
insert into part_to_composition values (1, 2, null, 3); 

insert into part_to_composition values (2, 2, null, 1);
insert into part_to_composition values (2, 3, null, 2);

insert into part_to_composition values (3, null, 1, 4);
insert into part_to_composition values (3, null, 2, 1);

update item set val=val*2 where id in (1,2);

insert into item values (4, 'item 4', 5);

delete from part_to_composition where id_item = 3;
insert into part_to_composition values (2, 4, null, 2);

update part_to_composition set cnt = cnt * 2;

Let's see if the triggers have correctly calculated val_computed:

select  c.id, i.id,
  substr(lpad(' ', 2*lvl-1) || nvl(c.txt, i.txt), 1, 20)                     txt,
  substr(case when p.cnt is not null then p.cnt || 'x' else null end,1, 10)  cnt,
    nvl(i.val, c.val_computed)                                               val
from (
  select rownum r, level lvl, c_node, p_node /*, p.cnt*/ from (
     select c_node, p_node /*, cnt*/ from (
         select distinct 'c' || id_composition c_node, null p_node /*,cnt*/ from part_to_composition minus
         select distinct 'c' || id_non_item    c_node, null p_node /*,cnt*/ from part_to_composition
          where id_non_item is not null
     ) roots
     union all -- non roots
        select case when id_item is not null then 'i' || id_item else 'c' || id_non_item end c_node, 'c' || id_composition p_node
          from part_to_composition
        )
    start with p_node is null
    connect by prior c_node = p_node
  ) tree left join part_to_composition p on (tree.c_node = 'c' || p.id_non_item and tree.p_node = 'c' || p.id_composition)
                                         or (tree.c_node = 'i' || p.id_item     and tree.p_node = 'c' || p.id_composition)
         left join item                i on  tree.c_node = 'i' || i.id
         left join composition         c on  tree.c_node = 'c' || c.id
order by tree.r;

        ID         ID TXT                  CNT               VAL
---------- ---------- -------------------- ---------- ----------
         3             comp 3                                312
         1               comp 1            8x                 32
                    1      item 1          4x                  2
                    2      item 2          6x                  4
         2               comp 2            2x                 28
                    2      item 2          2x                  4
                    4      item 4          4x                  5

Indeed, the val_computed for comp 2 is 28 (=2*4 + 4*5). Similarly for comp 1: 4*2 + 6*4 = 32, and for comp 3 8*32 + 2*28 = 312.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger