create table Node (
  Node_Id int not null identity primary key
);

create table Depend (
  Node_id int not null references Node (Node_id) on delete cascade,
  Parent_id int not null references Node (Node_id) on delete cascade,
  IsExtend tinyint not null default  0 ,
  primary key (Node_id, Parent_id),
  check (IsExtend in ( 0 ,  1 ))
);

select IsNull(CountNode) +  1  as Level
from (
  select count(Node_id) as CountNode
  from Depend
  where Node_id = @Node_id ) as t;

select Node_id
from Depend
where Parent_id = @Node_id
union all
select @Node as Node_id;

select Node_id, Parent_id
from Depend
where IsExtend =  0 
union all
select Node_id, null as Parent_id
from Node n
where not exists(
        select *
        from Depend
        where Node_id = n.Node_id and
              IsExtend =  0 )

create trigger trg_Depend_Insert
  after insert
  on Depend
  referencing new as Inserted
  for each statement
begin
  if VarExists('@@Depend_Oper') <>  0  then
    return;
  end if;

  create variable @@Depend_Oper tinyint;

   -- Добавляем связи родителей от родителей добавляемых узлов
 
  insert into Depend (Node_id, Parent_id, IsExtend)
    select t.Node_id, d.Parent_id,  1 
    from Inserted t
      inner join Depend d on d.Node_id = t.Parent_id
    where t.IsExtend =  0  and
      not exists(
        select *
        from Depend
        where Node_id = t.Node_id and 
              Parent_id = d.Parent_id);

   -- Вставляем родителями добавляемые узлы для дочерних узлов, ссылаемых на родителей
 
   -- добавляемых узлов
 
  insert into Depend (Node_id, Parent_id, IsExtend)
    select d.Node_id, t.Parent_id,  1 
    from Depend d
      inner join Depend p on p.Node_id = d.Parent_id and
                             p.IsExtend =  0 
      inner join Inserted t on t.Parent_id = p.Parent_id and
                               t.Node_id = p.Node_id and
                               t.IsExtend =  0 
    where not exists(
            select *
            from Depend
            where Node_id = d.Node_id and
                  Parent_id = t.Parent_id);

   -- Изменяем флаг родительских связей дочерних элементов на родителей
 
   -- добавляемых элементов, если есть совпадения родителей дочерних узлов и
 
   -- родителей добавляемых узлов
 
  update Depend d
  set IsExtend =  1 
  from Depend d
    inner join Inserted t on t.Node_id = d.Node_id and
                             t.IsExtend =  0 
    inner join Depend p on p.Node_id = t.Parent_id and
                           p.Parent_id = d.Parent_id and
                           p.IsExtend =  0 
  where d.IsExtend =  0 ;

  update Depend d
  set IsExtend =  1 
  from Depend d
    inner join Inserted t on t.Parent_id = d.Parent_id and
                             t.IsExtend =  0 
    inner join Depend p on p.Node_id = d.Node_id and
                           p.Parent_id = t.Node_id
  where d.IsExtend =  0 ;

   -- Вставляем родителями добавляемые узлы для всех дочерних узлов
 
  insert into Depend (Node_id, Parent_id, IsExtend)
    select d.Node_id, t.Node_id,  1 
    from Depend d
      inner join Depend p on p.Node_id = d.Parent_id
      inner join Inserted t on t.Node_id = p.Parent_id and
                               t.IsExtend =  0 
    where not exists(
            select *
            from Depend
            where Node_id = d.Node_id and
                  Parent_id = t.Node_id);

  drop variable @@Depend_Oper;

  exception
    when others then
      drop variable @@Depend_Oper;
      resignal;
end

create trigger trg_Depend_Delete
  after delete
  on Depend
  referencing old as Deleted
  for each statement
begin
  if VarExists('@@Depend_Oper') <>  0  then
    return;
  end if;

  create variable @@Depend_Oper tinyint;

   -- Устанавливаем родителей дочерних узлов на родителей удаляемых узлов
 
  update Depend d
  set IsExtend =  0 
  from Depend d
    inner join Depend p on p.Node_id = d.Node_id and
                           p.IsExtend =  0 
    inner join Deleted t on t.Node_id = p.Parent_id and
                            t.Parent_id = d.Parent_id and
                            t.IsExtend =  0 
  where d.IsExtend =  1 ;

   -- Удаляем вспомогательные связи удаляемых узлов
 
  delete Depend d
  from Depend d
    inner join Deleted t on t.Node_id = d.Node_id and
                            t.IsExtend =  0 
    inner join Depend p on p.Node_id = t.Parent_id and
                           p.Parent_id = d.Parent_id;

   -- Удаляем вспомогательные связи дочерних узлов 
 
   -- по отношению к удаляемым узлам
 
  delete Depend d
  from Depend d
    inner join Deleted t on t.Node_id = d.Parent_id and
                            t.IsExtend =  0 ;

  drop variable @@Depend_Oper;

  exception
    when others then
      drop variable @@Depend_Oper;
      resignal;
end

create trigger trg_Depend_update 
  before update
  on Depend
  for each row
begin
  if VarExists('@@Depend_Oper') =  0  then
    raiserror  20000  'Нельзя ручками изменять иеархию';
  end if;
end

 -- Указываем, что узел 2 является дочерним по отношению к узлу 1
 
insert into Depend (Node_id, Parent_id)
values ( 2 ,  1 );

 -- Указываем что узел 3 является дочерним по отношению к узлу 2
 
insert into Depend (Node_id, Parent_id)
values ( 3 ,  2 );

 -- Вставляем узел 4 между узлом 2 и 3
 
insert into Depend (Node_id, Parent_id)
  select  4 ,  2 
  union all
  select  3 ,  4 ;

 -- Удаляем узел 4, что приведет к тому, что узел 2 станет снова 
 
 -- прямым родителем узла 3
 
delete from Depend
where Node_id =  4  and Parent_id =  2 ;
 --