db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Barry Books" <trs...@gmail.com>
Subject Re: what is best way to extract parents and childs from a table with self reference?
Date Sun, 14 Jan 2007 14:12:25 GMT
There are a few ways to do this. I put a trigger on the category table
to build an ancestor table. This allows you to get all the records
with one sql statement by joining the ancestor table with the category
table where ancsestorid = root node. I only do inserts in to my table.
If you update you would also need update triggers.

drop table CategoryAncestors;
create table CategoryAncestors (
        categoryId      bigint,
        ancestorId      bigint,
        generation      int
);?


drop trigger CategoryPInsert;

create trigger CategoryPInsert
after insert on Categories
REFERENCING NEW as NewCategory
for each row mode DB2SQL
insert into CategoryAncestors ( categoryId, ancestorId, generation )
 (
  select NewCategory.categoryId, ancestorId, generation + 1
  from CategoryAncestors
  where categoryId = NewCategory.parentId
 union
  select NewCategory.categoryId, NewCategory.categoryId, 0 from sysibm.sysdummy1
 );

drop trigger CategoryInsert;
create trigger CategoryInsert
after insert on Categories
REFERENCING NEW as NewCategory
for each row mode DB2SQL
insert into CategoryAncestors ( categoryId, ancestorId, generation )
  (select NewCategory.categoryId, NewCategory.categoryId, 0 from
sysibm.sysdummy1
 );

Mime
View raw message