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

CREATE TRIGGER [NSZ].ORGANIZATION_AfterInsert ON [NSZ].T_ORGANIZATION
FOR INSERT 
AS
BEGIN
	DECLARE @left INT, @level INT, @parent uniqueidentifier, @id uniqueidentifier

	DECLARE curs CURSOR LOCAL FORWARD_ONLY STATIC
		FOR SELECT OrganizationID, 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 [NSZ].T_ORGANIZATION WHERE OrganizationID = @parent
				UPDATE [NSZ].T_ORGANIZATION SET LeftIndex = LeftIndex + 2 WHERE LeftIndex > @left
				UPDATE [NSZ].T_ORGANIZATION SET RightIndex = RightIndex + 2 WHERE RightIndex > @left
				UPDATE [NSZ].T_ORGANIZATION SET LeftIndex = @left + 1, RightIndex = @left + 2, TreeLevel = @level + 1
					WHERE OrganizationID =@id
			END
			ELSE
			BEGIN
				UPDATE [NSZ].T_ORGANIZATION SET LeftIndex = LeftIndex + 2, RightIndex = RightIndex + 2
				UPDATE [NSZ].T_ORGANIZATION SET LeftIndex = 1, RightIndex = 2, TreeLevel = 0 WHERE OrganizationID =@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'[NSZ].[ORGANIZATION_AfterDelete]'))
DROP TRIGGER [NSZ].[ORGANIZATION_AfterDelete]
GO

CREATE TRIGGER [NSZ].ORGANIZATION_AfterDelete ON [NSZ].T_ORGANIZATION 
FOR DELETE 
AS
BEGIN
	UPDATE t SET LeftIndex = t.LeftIndex -
		2 * (SELECT COUNT(*) FROM deleted d WHERE t.LeftIndex > d.LeftIndex)
		FROM [NSZ].T_ORGANIZATION 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 [NSZ].T_ORGANIZATION t WHERE t.RightIndex > ANY(SELECT LeftIndex FROM deleted)
END
go

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

CREATE TRIGGER [NSZ].ORGANIZATION_ChangeParent ON [NSZ].T_ORGANIZATION 
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.OrganizationID, 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.OrganizationID =d.OrganizationID
				LEFT JOIN [NSZ].T_ORGANIZATION t ON t.OrganizationID=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 [NSZ].T_ORGANIZATION 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 [NSZ].T_ORGANIZATION SET LeftIndex = LeftIndex + @spread
						WHERE LeftIndex < @left AND LeftIndex > 0
					UPDATE [NSZ].T_ORGANIZATION SET RightIndex = RightIndex + @spread
						WHERE RightIndex < @left AND RightIndex > 0
					UPDATE [NSZ].T_ORGANIZATION 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 [NSZ].T_ORGANIZATION SET LeftIndex = LeftIndex - @spread
							WHERE LeftIndex > @left AND LeftIndex <= @newleft
						UPDATE [NSZ].T_ORGANIZATION SET RightIndex = RightIndex - @spread
							WHERE RightIndex > @left AND RightIndex <= @newleft
					END
					ELSE
					BEGIN
						UPDATE [NSZ].T_ORGANIZATION SET LeftIndex = LeftIndex + @spread
							WHERE LeftIndex > @newleft AND LeftIndex < @left
						UPDATE [NSZ].T_ORGANIZATION SET RightIndex = RightIndex + @spread
							WHERE RightIndex > @newleft AND RightIndex < @left
					END
					
					SELECT @newleft = LeftIndex, @newright = RightIndex
						FROM [NSZ].T_ORGANIZATION WHERE OrganizationID = @newparent
					UPDATE [NSZ].T_ORGANIZATION 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].[RecalculateORGANIZATIONTreeParams]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RecalculateORGANIZATIONTreeParams]
GO

CREATE PROCEDURE [dbo].[RecalculateORGANIZATIONTreeParams]
	@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 [NSZ].T_ORGANIZATION WHERE ParentID IS NULL
	ELSE SELECT @ChCount = COUNT(*) FROM [NSZ].T_ORGANIZATION 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 OrganizationID FROM [NSZ].T_ORGANIZATION WHERE ParentID IS NULL
			OPEN curs1
			FETCH NEXT FROM curs1 INTO @ID
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				EXECUTE [dbo].RecalculateORGANIZATIONTreeParams @ParentID = @ID, @Begin = @Begin, @End = @End OUTPUT, @Level = @NestedLevel
				UPDATE [NSZ].T_ORGANIZATION SET LeftIndex=@Begin, RightIndex=@End, TreeLevel=@Level WHERE OrganizationID=@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 OrganizationID FROM [NSZ].T_ORGANIZATION WHERE ParentID=@ParentID
			OPEN curs1
			FETCH NEXT FROM curs1 INTO @ID
			WHILE (@@FETCH_STATUS <> -1)
			BEGIN
				EXECUTE [dbo].RecalculateORGANIZATIONTreeParams @ParentID = @ID, @Begin = @Begin, @End = @End OUTPUT, @Level = @NestedLevel 
				UPDATE [NSZ].T_ORGANIZATION SET LeftIndex=@Begin, RightIndex=@End, TreeLevel=@Level WHERE OrganizationID =@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].[RecalculateORGANIZATION]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RecalculateORGANIZATION]
GO

CREATE PROCEDURE [dbo].[RecalculateORGANIZATION]
as
BEGIN
	declare @end int
	exec [dbo].RecalculateORGANIZATIONTreeParams null, 1, @end output, 0
	alter table [NSZ].T_ORGANIZATION enable trigger all
END
go 	