Forum OpenACS Q&A: Nested set solution (cont.)

Deleting is no harder than inserting, and I've added a check that prevents deletion of rows having children (although referential integrity constraints would have jumped in anyway).

create function nested_set_delete_node () returns opaque as '
begin       
    --  We must prevent deletion of non-leaf nodes.
    if old.r_node - old.l_node <> 1 then
        raise exception ''Type "%" cannot be deleted, l_node = %  r_node = %'',
          old.object_type, old.l_node, old.r_node;
        return NULL;
    end if; 
    update acs_object_types set
        l_node = l_node - 2
        where l_node > old.r_node;
    update acs_object_types set
        r_node = r_node - 2
        where r_node > old.r_node;
    return old;
end;
' language 'plpgsql';

create trigger nested_set_delete_tr before delete
  on acs_object_types for each row
  execute procedure nested_set_delete_node() ;