--
-- acs_object_type_update_tr/0
--
create or replace function acs_object_type_update_tr(
  
) returns trigger as $$

declare
        v_parent_sk     varbit default null;
        v_max_value     integer;
        v_rec           record;
        clr_keys_p      boolean default 't';
begin
        if new.object_type = old.object_type and 
           ((new.supertype = old.supertype) or 
            (new.supertype is null and old.supertype is null)) then

           return new;

        end if;

        for v_rec in select object_type, supertype
                       from acs_object_types 
                      where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey)
                   order by tree_sortkey
        LOOP
            if clr_keys_p then
               update acs_object_types set tree_sortkey = null
               where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey);
               clr_keys_p := 'f';
            end if;
            
            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
              from acs_object_types 
              where supertype = v_rec.supertype;

            select tree_sortkey into v_parent_sk 
              from acs_object_types 
             where object_type = v_rec.supertype;

            update acs_object_types 
               set tree_sortkey = tree_next_key(v_parent_sk, v_max_value)
             where object_type = v_rec.object_type;

        end LOOP;

        return new;

end;$$ language plpgsql;