/****** 
    Object:  StoredProcedure [dbo].[sp_InsertTreeNodeAndReturnTreeStructureId]    
    Script Date: 08/16/2008 09:29:07 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertTreeNodeAndReturnTreeStructureId]
	(
		@TreeStructureId int,    -- Parent Node
		@TreeId int,                -- TreeId (in case no TreeStructures exist yet for the Tree)
		@Index int                  -- Index where to be inserted (if null, insert at last place)
	)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	DECLARE @TranName NVARCHAR(MAX)
	SELECT @TranName = 'InsertTreeNodeAndReturnTreeStructureId';
	BEGIN TRANSACTION @TranName
	BEGIN TRY

		DECLARE @RightValue int
		DECLARE @TargetTreeStructureId int
		IF (@TreeStructureId IS NULL)
		   -- Tree exists but no TreeStructures yet for Tree 
                   BEGIN
			SET @RightValue = 1
		    END
		ELSE
		    BEGIN
			IF(@TreeId IS NULL)
                            -- With TreeStructureId it's easy to get the TreeId
			    BEGIN
			        SELECT     @TreeId = TreeId 
                                FROM         TreeStructures 
                                WHERE       TreeStructureId = @TreeStructureId
			    END
			IF (@Index IS NULL OR @Index = -1)
			    BEGIN
			        -- Node will be inserted at last place
				SELECT	@RightValue = RightValue
				FROM	        TreeStructures
				WHERE	TreeStructureId = @TreeStructureId
			    END
			ELSE
			    BEGIN
			        SET @TargetTreeStructureId = 
                                    dbo.[fn_Edumatic3_GetChildTreeStructure](@TreeStructureId, @Index)		
				IF(@TargetTreeStructureId <> -1)
				    BEGIN
				        SELECT  @RightValue = LeftValue
					FROM	    TreeStructures
					WHERE   TreeStructureId = dbo.[fn_GetChildTreeStructure](@TreeStructureId, @Index)
				    END
					ELSE
					    BEGIN
						SELECT	@RightValue = RightValue
						FROM  	TreeStructures
						WHERE	TreeStructureId = @TreeStructureId
					    END
			    END

			-- Move TreeStructure row
			UPDATE  TreeStructures 
                        SET       RightValue = RightValue + 2 
                        WHERE   RightValue > (@RightValue - 1) 
                        AND       TreeId = @TreeId
			
                        UPDATE  TreeStructures 
                        SET       LeftValue = LeftValue + 2 
                        WHERE   LeftValue > (@RightValue - 1) 
                        AND       TreeId = @TreeId
		   END
		
	       -- Insert new TreeStructure row
	       INSERT 
               INTO     TreeStructures (TreeId, LeftValue, RightValue) 
               VALUES  (@TreeId, @RightValue, @RightValue + 1)

	       COMMIT TRANSACTION @TranName

	       RETURN @TreeStructureId
	END TRY

	BEGIN CATCH
		
		/* If an error occured we Roll Back the transaction and 
                    raise an error that VS can catch */

		ROLLBACK TRANSACTION @TranName
		RAISERROR 50001 'Error sp_InsertTreeNodeAndReturnTreeStructureId. 
                                         Rollback has been executed.'

		/*************************************************************/	

	END CATCH
	
END