
--Добавление записей:
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[VCLib].[DIVISIONS_AfterInsert]'))
DROP TRIGGER [VCLib].[DIVISIONS_AfterInsert]
GO

CREATE TRIGGER DIVISIONS_AfterInsert ON VCLib.T_DIVISIONS
FOR INSERT 
AS
BEGIN
	DECLARE @left INT, @level INT, @parent uniqueidentifier, @id uniqueidentifier

	DECLARE curs CURSOR LOCAL FORWARD_ONLY STATIC
		FOR SELECT DivisionID, ParentID FROM inserted
	OPEN curs
	FETCH NEXT FROM curs INTO @id, @parent
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		IF (@id IS NOT NULL)
		BEGIN
			IF (@parent IS NOT NULL)
			BEGIN
				SELECT @left = LeftIndex, @level = TreeLevel FROM VCLib.T_DIVISIONS WHERE DivisionID = @parent
				UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + 2 WHERE LeftIndex > @left
				UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex + 2 WHERE RightIndex > @left
				UPDATE VCLib.T_DIVISIONS SET LeftIndex = @left + 1, RightIndex = @left + 2, TreeLevel = @level + 1
					WHERE DivisionID =@id
			END
			ELSE
			BEGIN
				UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + 2, RightIndex = RightIndex + 2
				UPDATE VCLib.T_DIVISIONS SET LeftIndex = 1, RightIndex = 2, TreeLevel = 0 WHERE DivisionID =@id
			END
		
			FETCH NEXT FROM curs INTO @id, @parent
		END
	END
	CLOSE curs
	DEALLOCATE curs
END
go

--Удаление записей:
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[VCLib].[DIVISIONS_AfterDelete]'))
DROP TRIGGER [VCLib].[DIVISIONS_AfterDelete]
GO

CREATE TRIGGER DIVISIONS_AfterDelete ON VCLib.T_DIVISIONS 
FOR DELETE 
AS
BEGIN
	UPDATE t SET LeftIndex = t.LeftIndex -
		2 * (SELECT COUNT(*) FROM deleted d WHERE t.LeftIndex > d.LeftIndex)
		FROM VCLib.T_DIVISIONS t WHERE t.LeftIndex > ANY(SELECT LeftIndex FROM deleted)
	UPDATE t SET RightIndex = t.RightIndex -
		2 * (SELECT COUNT(*) FROM deleted d WHERE t.RightIndex > d.RightIndex)
		FROM VCLib.T_DIVISIONS t WHERE t.RightIndex > ANY(SELECT LeftIndex FROM deleted)
END
go

--Изменение родителей записей:
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[VCLib].[DIVISIONS_ChangeParent]'))
DROP TRIGGER [VCLib].[DIVISIONS_ChangeParent]
GO

CREATE TRIGGER DIVISIONS_ChangeParent ON VCLib.T_DIVISIONS 
FOR UPDATE
AS
BEGIN
	IF (UPDATE(ParentID))
	BEGIN

		DECLARE @left INT, @right INT, @level INT, @parent uniqueidentifier, @id uniqueidentifier, @spread INT
		DECLARE @newparent uniqueidentifier, @newleft INT, @newright INT, @newlevel INT
		
		DECLARE curs CURSOR LOCAL FORWARD_ONLY STATIC
			FOR SELECT d.DivisionID, d.ParentID, d.LeftIndex, d.RightIndex, d.TreeLevel, i.ParentID,
				t.LeftIndex, t.RightIndex, t.TreeLevel
				FROM deleted d INNER JOIN inserted i ON i.DivisionID =d.DivisionID
				LEFT JOIN VCLib.T_DIVISIONS t ON t.DivisionID=i.ParentID
		OPEN curs
		FETCH NEXT FROM curs INTO @id, @parent, @left, @right, @level,
			@newparent, @newleft, @newright, @newlevel
		WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		
			IF (@id IS NOT NULL AND @left IS NOT NULL AND @right IS NOT NULL)
			BEGIN
				
				UPDATE VCLib.T_DIVISIONS SET LeftIndex = -LeftIndex, RightIndex = -RightIndex
					WHERE LeftIndex >= @left AND RightIndex <= @right
				
				SET @spread = @right - @left + 1
				
				IF (@newparent IS NULL AND @parent IS NOT NULL)
				BEGIN
					UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + @spread
						WHERE LeftIndex < @left AND LeftIndex > 0
					UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex + @spread
						WHERE RightIndex < @left AND RightIndex > 0
					UPDATE VCLib.T_DIVISIONS SET LeftIndex = 1 - (@left + LeftIndex),
						RightIndex = 1 - (@left + RightIndex), TreeLevel = TreeLevel - @level
						WHERE LeftIndex <= -@left AND RightIndex >= -@right
				END
				ELSE
				BEGIN
					IF (@left < @newleft)
					BEGIN
						UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex - @spread
							WHERE LeftIndex > @left AND LeftIndex <= @newleft
						UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex - @spread
							WHERE RightIndex > @left AND RightIndex <= @newleft
					END
					ELSE
					BEGIN
						UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + @spread
							WHERE LeftIndex > @newleft AND LeftIndex < @left
						UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex + @spread
							WHERE RightIndex > @newleft AND RightIndex < @left
					END
					
					SELECT @newleft = LeftIndex, @newright = RightIndex
						FROM VCLib.T_DIVISIONS WHERE DivisionID = @newparent
					UPDATE VCLib.T_DIVISIONS SET LeftIndex = @newleft - (@left + LeftIndex) + 1,
						RightIndex = @newleft - (@left + RightIndex) + 1,
						TreeLevel = @newlevel + (TreeLevel - @level) + 1
						WHERE LeftIndex <= -@left AND RightIndex >= -@right
				END
			END

			FETCH NEXT FROM curs INTO @id, @parent, @left, @right, @level,
				@newparent, @newleft, @newright, @newlevel
		END
		CLOSE curs
		DEALLOCATE curs

	END
END
go

--- пересчет служебных полей
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecalculateDivisionsTreeParams]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RecalculateDivisionsTreeParams]
GO

CREATE PROCEDURE [dbo].[RecalculateDivisionsTreeParams]
	@ParentID uniqueidentifier = NULL,
	@Begin INT = 1,
	@End INT OUTPUT,
	@Level INT = 0
AS
BEGIN
	DECLARE @ChCount as int
	IF (@ParentID IS NULL) SELECT @ChCount = COUNT(*) FROM VCLib.T_DIVISIONS WHERE ParentID IS NULL
	ELSE SELECT @ChCount = COUNT(*) FROM VCLib.T_DIVISIONS WHERE ParentID=@ParentID
	
	IF (@ChCount = 0 or @ChCount is NULL)
	BEGIN
		SET @End = @Begin + 1
	END
	ELSE
	BEGIN
		DECLARE @ID as uniqueidentifier, @NestedLevel INT
		SET @NestedLevel = @Level + 1
		
		IF (@ParentID IS NULL)
		BEGIN
			SET @Begin = 1
			DECLARE curs1 CURSOR LOCAL FORWARD_ONLY STATIC
				FOR SELECT DivisionID FROM VCLib.T_DIVISIONS WHERE ParentID IS NULL
			OPEN curs1
			FETCH NEXT FROM curs1 INTO @ID
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				EXECUTE RecalculateDivisionsTreeParams @ParentID = @ID, @Begin = @Begin, @End = @End OUTPUT, @Level = @NestedLevel
				UPDATE VCLib.T_DIVISIONS SET LeftIndex=@Begin, RightIndex=@End, TreeLevel=@Level WHERE DivisionID=@ID
				SET @Begin = @End + 1
				FETCH NEXT FROM curs1 INTO @ID
			END
			CLOSE curs1
			DEALLOCATE curs1
		END
		ELSE
		BEGIN
			SET @Begin = @Begin + 1
			DECLARE curs1 CURSOR LOCAL FORWARD_ONLY STATIC
				FOR SELECT DivisionID FROM VCLib.T_DIVISIONS WHERE ParentID=@ParentID
			OPEN curs1
			FETCH NEXT FROM curs1 INTO @ID
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				EXECUTE RecalculateDivisionsTreeParams @ParentID = @ID, @Begin = @Begin, @End = @End OUTPUT, @Level = @NestedLevel 
				UPDATE VCLib.T_DIVISIONS SET LeftIndex=@Begin, RightIndex=@End, TreeLevel=@Level WHERE DivisionID =@ID
				SET @Begin = @End + 1
				FETCH NEXT FROM curs1 INTO @ID
			END
			CLOSE curs1
			DEALLOCATE curs1
		END
		
		SET @End = @End + 1
	END
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecalculateDIVISIONS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RecalculateDIVISIONS]
GO

CREATE PROCEDURE [dbo].[RecalculateDIVISIONS]
as
BEGIN
	declare @end int
	alter table VCLib.T_DIVISIONS disable trigger all
	exec dbo.RecalculateDivisionsTreeParams null, 1, @end output, 0
	alter table VCLib.T_DIVISIONS enable trigger all
END
go 	