ALTER TABLE T1
  ADD L Integer;
  
ALTER TABLE T1
  ADD R Integer;  
  
CREATE UNIQUE INDEX T1_L ON T1 (L);
CREATE UNIQUE INDEX T1_R ON T1 (R);


CREATE OR ALTER TRIGGER T1_Tree_Changed FOR T1
ACTIVE AFTER INSERT OR UPDATE 
AS
begin
  if ((NEW.id <> OLD.id) or (NEW.parent is distinct from OLD.Parent)) then
    if ((old.l is not null) or (old.r is not null)) then
    begin
      update T1 set l = null, r = null;
      -- new.l = null;  needed for before-xxx triggers
      -- new.r = null;
    end
end  



CREATE OR ALTER TRIGGER T1_PK FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (new.ID IS NULL) then
      SELECT NEXT VALUE FOR T1_GEN from rdb$database into new.ID;
end



create or alter procedure UPD_T1_LR_1 (
    R_IN integer = null,
    ITEM_ID integer = null)
returns (
    R_OUT integer)
as
declare variable L_VAR integer;
declare variable LOOP_ID integer;
begin
 R_OUT = 0;
 if (exists( select * from t1 where id = :item_id )) then begin
   L_Var = coalesce(:R_IN, 0) + 1;
   R_OUT = :L_Var;
 end

 for select id from t1
     where parent is not distinct from :item_id
     order by id  -- ordering is not supported in With Recursive
     into loop_id
 do
 begin
   execute procedure UPD_t1_LR_1 (:R_OUT, :loop_id)
                returning_values (:R_OUT);
 end

 R_OUT = :R_OUT + 1;
 update t1 set
   L = :l_var, 
   R = :r_out
 where id = :item_id;
end^