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

CREATE TRIGGER [APS].MKB_AfterInsert ON [APS].T_MKB
FOR INSERT 
AS
BEGIN
	DECLARE @left INT, @level INT, @parent uniqueidentifier, @id uniqueidentifier

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

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

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

CREATE TRIGGER [APS].MKB_ChangeParent ON [APS].T_MKB 
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.MkbID, 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.MkbID =d.MkbID
				LEFT JOIN [APS].T_MKB t ON t.MkbID=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 [APS].T_MKB 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 [APS].T_MKB SET LeftIndex = LeftIndex + @spread
						WHERE LeftIndex < @left AND LeftIndex > 0
					UPDATE [APS].T_MKB SET RightIndex = RightIndex + @spread
						WHERE RightIndex < @left AND RightIndex > 0
					UPDATE [APS].T_MKB 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 [APS].T_MKB SET LeftIndex = LeftIndex - @spread
							WHERE LeftIndex > @left AND LeftIndex <= @newleft
						UPDATE [APS].T_MKB SET RightIndex = RightIndex - @spread
							WHERE RightIndex > @left AND RightIndex <= @newleft
					END
					ELSE
					BEGIN
						UPDATE [APS].T_MKB SET LeftIndex = LeftIndex + @spread
							WHERE LeftIndex > @newleft AND LeftIndex < @left
						UPDATE [APS].T_MKB SET RightIndex = RightIndex + @spread
							WHERE RightIndex > @newleft AND RightIndex < @left
					END
					
					SELECT @newleft = LeftIndex, @newright = RightIndex
						FROM [APS].T_MKB WHERE MkbID = @newparent
					UPDATE [APS].T_MKB 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
