-- NOTE: Organizations have id and parentOrgId columns
-- OrgClosure has parentId, childId, depth columns

CREATE OR REPLACE TRIGGER Org_Add_Trigger
    AFTER INSERT ON Organization
    REFERENCING NEW AS NEW
    FOR EACH ROW
BEGIN
    -- must insert in this order...
    insert into OrgClosure (parentId, childId, depth)
    VALUES (NEW.id, NEW.id, 0);

    insert into OrgClosure (parentId, childId, depth)
    select p.parentId, c.childId, p.depth+c.depth+1 as depth
    from OrgClosure p, OrgClosure c
    where p.childId=NEW.parentOrgId and c.parentId=NEW.id;
END