|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
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.