[/src]
print 'Creating table TreeDepartments... '

go

create table TreeDepartments

(

DepID smallint not null,

ParentDep smallint default( 0 ), 

Level smallint default( 0 ),

ParentLevel smallint default( 0 ),

constraint pkTreeDepartments primary key clustered (DepID,ParentDep), 

constraint fkTreeDepartments foreign key (DepID) references Departments, 

constraint fkTreeDepartmentParent foreign key (ParentDep) references Departments

)

go

print 'Creating tr_TreeDepartmentsIns'

go

if exists (select * from sysobjects where name = 'tr_TreeDepartmentsIns')

drop trigger tr_TreeDepartmentsIns

go

create trigger tr_TreeDepartmentsIns on Departments FOR INSERT

as

begin

declare @e int,@level int,@DepID int,@Parent int

 /*Принятие решения об использовании курсора сделано во избежание использования подзапроса на таблицу    TreeDepartments для выяснения собственного уровня (заменено @@rowcownt )*/ 

declare tr cursor local fast_forward for

select DepID,ParentDep from inserted 

open tr

while ( 1 = 1 )

   begin

        fetch tr into @DepID,@Parent

        if (@@fetch_status<> 0 ) break

        select @DepID a ,r.ParentDep b , 0  c ,r.ParentLevel d into #t

        from TreeDepartments r 

        where r.DepID=@Parent and @DepID<>r.DepID and r.DepID<>r.Parentdep

 

        select @level=@@rowcount,@e = @@error 

    

        insert into TreeDepartments (DepID,ParentDep,Level,ParentLevel)

        select a,b,@level+ 1 ,d from #t 

    

        if (@e !=  0 )

            begin

                deallocate tr

                rollback 

                tran raiserror ('tr_TreeDepartmentsIns: Вставка родственных связей',  16 , - 1 ) 

                return 

            end    

    

        insert into TreeDepartments (DepID,ParentDep,Level,ParentLevel)

        Values(@DepID,@Parent,@Level+ 1 ,@Level)

        if (@@error !=  0 )

            begin

                deallocate tr

                rollback tran 

                raiserror ('tr_TreeDepartmentsIns: Вставка родственных связей',  16 , - 1 ) 

                return 

            end

  end

Close tr

deallocate tr

end

go

 

print 'Creating tr_TreeDepartmentsDel'

go

if exists (select * from sysobjects where name = 'tr_TreeDepartmentsDel')

drop trigger tr_TreeDepartmentsDel

go

create trigger tr_TreeDepartmentsDel on Departments INSTEAD OF Delete as

begin

    If exists (select top  1  * from deleted d inner join TreeDepartments r on d.DepID=r.ParentDep)

        begin 

            rollback tran 

            raiserror ('tr_TreeDepartmentsDel: Нарушение целосности ',  16 , - 1 ) 

            return 

        end

    delete t from deleted d inner join TreeDepartments t on d.DepID=t.DepID

    if (@@error !=  0 )

        begin

            deallocate tr

            rollback tran 

            raiserror ('tr_TreeDepartmentsDel: Удаление родственных связей',  16 , - 1 ) 

            return 

        end

    delete t from deleted d inner join Departments t on d.DepID=t.DepID

    if (@@error !=  0 )

        begin

            deallocate tr

            rollback tran 

            raiserror ('tr_TreeDepartmentsDel: Удаление родственных связей',  16 , - 1 ) 

            return 

        end

end

go

 

print 'Creating tr_TreeDepartmentsUpd'

go

if exists (select * from sysobjects where name = 'tr_TreeDepartmentsUpd')

drop trigger tr_TreeDepartmentsUpd

go

 

create trigger tr_TreeDepartmentsUpd on Departments FOR Update  as

begin

IF UPDATE(ParentDep) 

    begin

    If exists (select top  1  * from deleted d inner join TreeDepartments r on d.DepID=r.ParentDep)

        begin 

            rollback tran 

            raiserror ('tr_TreeDepartmentsUpd: Нарушение целосности ',  16 , - 1 ) 

            return 

        end

    delete t from deleted d inner join TreeDepartments t on d.DepID=t.DepID 

    if (@@error !=  0 )

    begin

        deallocate tr

        rollback tran 

        raiserror ('tr_TreeDepartmentsUpd: Удаление родственных связей',  16 , - 1 ) 

        return 

    end

 

    declare @e int,@level int,@DepID int,@Parent int

    declare tr cursor local fast_forward for

    select DepID,ParentDep from inserted

    open tr

    while ( 1 = 1 )

    begin

        fetch tr into @DepID,@Parent

        if (@@fetch_status<> 0 ) break

        select @DepID a ,r.ParentDep b , 0  c ,r.ParentLevel d into #t

        from TreeDepartments r

        where r.DepID=@Parent and @DepID<>r.DepID and r.DepID<>r.Parentdep

 

        select @level=@@rowcount,@e = @@error

 

        insert into TreeDepartments (DepID,ParentDep,Level,ParentLevel)

 

        select a,b,@level+ 1 ,d from #t 

    

        if (@e !=  0 )

        begin

        deallocate tr

        rollback tran 

        raiserror ('tr_TreeDepartmentsIns: Вставка родственных связей',  16 , - 1 ) 

        return 

        end

        insert into TreeDepartments (DepID,ParentDep,Level,ParentLevel)

        Values(@DepID,@Parent,@Level+ 1 ,@Level)

 

        if (@e !=  0 )

        begin

        deallocate tr

        rollback tran 

        raiserror ('tr_TreeDepartmentsIns: Вставка родственных связей',  16 , - 1 ) 

        return 

        end

    end

    Close tr

    deallocate tr

    end

end

go

 /*Инициализируем таблицу TreeDepartments */ 

insert into TreeDepartments(DepID,ParentDep,Level,ParentLevel)

select DepID,DepID, 0 , 0  from Departments where ParentDep is Null

 

declare @rc int,@c int,@c1 int

select @rc= 1 ,@c= 1 

 

while @rc> 0 

    begin

    select @c1= 1 

 

    insert into TreeDepartments(DepID,ParentDep,Level,ParentLevel)

    select d.DepID,d.ParentDep,@c,@c- 1  

    from TreeDepartments t inner join Departments d

        on t.DepID=d.ParentDep and t.Level=@c- 1  and d.DepID<>d.ParentDep

 

    select @rc=@@Rowcount

    while @c1<=@c

        begin

 

        insert into TreeDepartments(DepID,ParentDep,Level,ParentLevel)

        select t.DepID,d.ParentDep,t.Level,d.ParentLevel 

        from TreeDepartments t inner join TreeDepartments d

            on d.DepID=t.ParentDep and  t.level=@c- 1  and d.ParentLevel=@c1- 1   

            where t.DepID<>t.ParentDep and not exists

            (select * from TreeDepartments t1 where t1.DepID=t.DepID

            and t1.ParentDep=d.ParentDep)

 

 

        select @rc=@rc+@@Rowcount

        select @c1=@c1+ 1 

    end

    select @c=@c+ 1 

end

 

[src]