Tree utilities in SQL Server 2000 and 2005, and OLAP implementations

��IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TREE_DB')

	DROP DATABASE [TREE_DB]

GO



CREATE DATABASE [TREE_DB]  ON (NAME = N'TREE_DB_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\TREE_DB_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'TREE_DB_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\TREE_DB_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

 COLLATE Latin1_General_CI_AS

GO



exec sp_dboption N'TREE_DB', N'autoclose', N'true'

GO



exec sp_dboption N'TREE_DB', N'bulkcopy', N'false'

GO



exec sp_dboption N'TREE_DB', N'trunc. log', N'true'

GO



exec sp_dboption N'TREE_DB', N'torn page detection', N'true'

GO



exec sp_dboption N'TREE_DB', N'read only', N'false'

GO



exec sp_dboption N'TREE_DB', N'dbo use', N'false'

GO



exec sp_dboption N'TREE_DB', N'single', N'false'

GO



exec sp_dboption N'TREE_DB', N'autoshrink', N'true'

GO



exec sp_dboption N'TREE_DB', N'ANSI null default', N'false'

GO



exec sp_dboption N'TREE_DB', N'recursive triggers', N'false'

GO



exec sp_dboption N'TREE_DB', N'ANSI nulls', N'false'

GO



exec sp_dboption N'TREE_DB', N'concat null yields null', N'false'

GO



exec sp_dboption N'TREE_DB', N'cursor close on commit', N'false'

GO



exec sp_dboption N'TREE_DB', N'default to local cursor', N'false'

GO



exec sp_dboption N'TREE_DB', N'quoted identifier', N'false'

GO



exec sp_dboption N'TREE_DB', N'ANSI warnings', N'false'

GO



exec sp_dboption N'TREE_DB', N'auto create statistics', N'true'

GO



exec sp_dboption N'TREE_DB', N'auto update statistics', N'true'

GO



use [TREE_DB]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_SET_LEFT_RIGHT]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)

drop trigger [dbo].[TREE_SET_LEFT_RIGHT]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnGetDaysNumber]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[fnGetDaysNumber]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnIsLeapYear]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[fnIsLeapYear]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TIME_BY_DAY_GEN]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TIME_BY_DAY_GEN]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_2_OLAP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TREE_2_OLAP]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_GET_XML]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TREE_GET_XML]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_RECALC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TREE_RECALC]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_RECALC_ITER]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TREE_RECALC_ITER]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_RECALC_REC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TREE_RECALC_REC]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SALES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[SALES]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TIME_BY_DAY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TIME_BY_DAY]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TREE]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TREE_OLAP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TREE_OLAP]

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE FUNCTION fnGetDaysNumber(

		@YEAR INT

		, @MONTH INT

)

RETURNS INT

AS

BEGIN



	DECLARE @days INT

	SET @days = CASE @MONTH 

		WHEN 1 THEN 31

		WHEN 3 THEN 31

		WHEN 5 THEN 31

		WHEN 7 THEN 31

		WHEN 8 THEN 31

		WHEN 10 THEN 31

		WHEN 12 THEN 31

		WHEN 4 THEN 30

		WHEN 6 THEN 30

		WHEN 8 THEN 30

		WHEN 9 THEN 30

		WHEN 11 THEN 30

		WHEN 2 THEN

			CASE dbo.fnIsLeapYear(@YEAR) 

				WHEN 1 THEN 29

				ELSE 28

			END

	END

	RETURN @days

END





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE FUNCTION fnIsLeapYear(

		@YEAR INT

)

RETURNS BIT

AS

BEGIN

	IF @year % 100 = 0

		IF @YEAR % 400 = 0

			RETURN 1

	ELSE

		IF (@YEAR % 4) = 0

			RETURN 1

	RETURN 0

END





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



CREATE TABLE [dbo].[SALES] (

	[ID] [int] IDENTITY (1, 1) NOT NULL ,

	[TREE_ID] [int] NOT NULL ,

	[TIME_ID] [int] NOT NULL ,

	[SALES_VALUES] [float] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[TIME_BY_DAY] (

	[ID] [int] IDENTITY(1, 1) NOT NULL ,

	[T_DATE] [smalldatetime] NULL ,

	[T_DAY] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,

	[T_MONTH] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,

	[T_YEAR] [smallint] NULL ,

	[DAY_OF_MONTH] [smallint] NULL ,

	[WEEK_OF_YEAR] [float] NULL ,

	[MONTH_OF_YEAR] [smallint] NULL ,

	[QUARTER] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,

	[FISCAL_PERIOD] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[TREE] (

	[ID] [int] NOT NULL ,

	[P_ID] [int] NULL ,

	[NAME] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[NLEFT] [int] NOT NULL ,

	[NRIGHT] [int] NOT NULL ,

	[NLEVEL] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[TREE_OLAP] (

	[ID] [int] NULL ,

	[N1] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[N2] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[N3] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[N4] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[N5] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[N6] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

	[N7] [varchar] (100) COLLATE Latin1_General_CI_AS NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[TREE] ADD 

	CONSTRAINT [DF_TREE_P_LEFT] DEFAULT ((-1)) FOR [NLEFT],

	CONSTRAINT [DF_TREE_P_RIGHT] DEFAULT ((-1)) FOR [NRIGHT],

	CONSTRAINT [DF_TREE_NLEVEL] DEFAULT ((-1)) FOR [NLEVEL],

	CONSTRAINT [PK_TREE] PRIMARY KEY  CLUSTERED 

	(

		[ID]

	)  ON [PRIMARY] 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE PROCEDURE TIME_BY_DAY_GEN(

		@YEAR INT

)

AS

/*

DECLARE @YEAR INT

SET @YEAR = 2006

*/



IF EXISTS(SELECT * FROM TIME_BY_DAY WHERE T_YEAR = @YEAR)

BEGIN

	DELETE FROM TIME_BY_DAY WHERE T_YEAR = @YEAR

END



DECLARE @DATE DATETIME

DECLARE @MONTH INT

DECLARE @DAY INT

DECLARE @DAYS INT



DECLARE @Y VARCHAR(4)

DECLARE @M VARCHAR(2)

DECLARE @D VARCHAR(2)





SET @MONTH = 1

SET @Y = LTRIM(RTRIM(STR(@YEAR)))





WHILE @MONTH <= 12

BEGIN



	SET @M = LTRIM(RTRIM(STR(@MONTH)))

	SET @M = CASE WHEN @MONTH < 10 THEN '0' + @M ELSE @M END

	SET @DAYS = dbo.fnGetDaysNumber(@YEAR, @MONTH)

	SET @DAY = 1





	WHILE @DAY <= @DAYS

	BEGIN

		SET @D = LTRIM(RTRIM(STR(@DAY)))

		SET @D = CASE WHEN @DAY < 10 THEN '0' + @D ELSE @D END

		SET @DATE = @Y + '-' + @M + '-' + @D

		INSERT INTO TIME_BY_DAY VALUES(@DATE, DATENAME(DW, @DATE), DATENAME(MM, @DATE), @YEAR, @DAY, DATEPART(WW, @DATE), @MONTH, 'Q' + LTRIM(RTRIM(STR(DATEPART(Q, @DATE)))), NULL)

		SET @DAY = @DAY + 1

	END

	SET @MONTH = @MONTH + 1

END





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO







CREATE PROCEDURE TREE_2_OLAP(

		@ROOT_ID INT

)

AS



/*

DECLARE @ROOT_ID INT

SET @ROOT_ID = 1

*/

DECLARE @ID INT

DECLARE @P_ID INT

DECLARE @NAME VARCHAR(100)

DECLARE @NLEVEL INT



DECLARE @ID2 INT

DECLARE @P_ID2 INT

DECLARE @NAME2 VARCHAR(100)

DECLARE @NLEVEL2 INT

DECLARE @NLEVEL2_REAL INT



DECLARE @SQL NVARCHAR(4000)

DECLARE @I INT

DECLARE @MAX_LEVEL INT



SELECT @MAX_LEVEL = ISNULL(MAX(C.NLEVEL), 0)

FROM TREE P

INNER JOIN TREE C ON C.NLEFT BETWEEN P.NLEFT AND P.NRIGHT

WHERE P.ID = @ROOT_ID



IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TREE_OLAP')

BEGIN

	DROP TABLE TREE_OLAP

END



SET @SQL = N''

SET @SQL = @SQL + N'

CREATE TABLE TREE_OLAP(ID INT'

SET @I = 1

WHILE @I <= @MAX_LEVEL

BEGIN

	SET @SQL = @SQL + N'

	, N' + LTRIM(RTRIM(STR(@I))) + N' VARCHAR(100)

'

	SET @I = @I + 1

END



SET @SQL = @SQL + N')'



EXEC sp_executesql @SQL



--BEGIN STACK PARENT TO CHILD



CREATE TABLE #TPC(ID INT, P_ID INT, NAME VARCHAR(100), NLEVEL INT)

CREATE TABLE #TCP(ID INT, P_ID INT, NAME VARCHAR(100), NLEVEL INT)



SET @NLEVEL = 1

INSERT INTO #TPC SELECT @ROOT_ID, P_ID, NAME, @NLEVEL FROM TREE WHERE ID = @ROOT_ID



WHILE @NLEVEL > 0

BEGIN

	IF EXISTS(SELECT * FROM #TPC WHERE NLEVEL = @NLEVEL)

	BEGIN

		SELECT TOP 1 @ID = ID, @P_ID = P_ID, @NAME = NAME FROM #TPC WHERE NLEVEL = @NLEVEL ORDER BY ID



		SET @SQL = N'INSERT INTO TREE_OLAP VALUES(@ID'

		SET @I = 1

		WHILE @I <= @MAX_LEVEL

		BEGIN

			IF @I = @NLEVEL

				SET @SQL = @SQL + N', @NAME'

			ELSE

				SET @SQL = @SQL + N', ''<none>'''

			SET @I = @I + 1

		END

		SET @SQL = @SQL + N')'

		PRINT @SQL

		EXEC sp_executesql @SQL, N'@ID INT, @NAME VARCHAR(100)', @ID = @ID, @NAME = @NAME



-- BEGIN STACK CHILD TO PARENT

	

		TRUNCATE TABLE #TCP



		SET @NLEVEL2 = @NLEVEL - 1

		INSERT INTO #TCP SELECT ID, P_ID, NAME, @NLEVEL2 FROM TREE WHERE ID = @P_ID



		WHILE @NLEVEL2 <= @NLEVEL

		BEGIN

			IF EXISTS(SELECT * FROM #TCP WHERE NLEVEL = @NLEVEL2)

			BEGIN

				SELECT @ID2 = ID, @P_ID2 = P_ID, @NAME2 = NAME FROM #TCP WHERE NLEVEL = @NLEVEL2

				SET @SQL = N'UPDATE TREE_OLAP SET N' + LTRIM(RTRIM(STR(@NLEVEL2))) + ' = @NAME WHERE ID = @ID'

				EXEC sp_executesql @SQL, N'@ID INT, @NAME VARCHAR(100)', @ID = @ID, @NAME = @NAME2

				DELETE FROM #TCP WHERE NLEVEL = @NLEVEL2 AND ID = @ID2

				INSERT INTO #TCP SELECT ID, P_ID, NAME, @NLEVEL2 - 1 FROM TREE WHERE ID = @P_ID2

				IF @@ROWCOUNT > 0

					SET @NLEVEL2 = @NLEVEL2 - 1

			END

			ELSE

			BEGIN

				SET @NLEVEL2 = @NLEVEL2 + 1

			END

		END





--END STACK CHILD TO PARENT



		DELETE FROM #TPC WHERE NLEVEL = @NLEVEL AND ID = @ID

		INSERT INTO #TPC SELECT ID, P_ID, NAME, @NLEVEL + 1 FROM TREE WHERE P_ID = @ID

		IF @@ROWCOUNT > 0

			SET @NLEVEL = @NLEVEL + 1

	END

	ELSE

	BEGIN

		SET @NLEVEL = @NLEVEL - 1

	END

END



SELECT * FROM TREE_OLAP



DROP TABLE #TPC

DROP TABLE #TCP



--END STACK PARENT TO CHILD





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE PROCEDURE TREE_GET_XML(

		@ROOT_ID INT

)

AS

/*

DECLARE @ROOT_ID INT

SET @ROOT_ID = 52

*/

SELECT 

	1 AS TAG

	, NULL AS PARENT

	, '' AS [root!1]

	, NULL AS [node!2!id]

	, NULL AS [node!2!p_id]

	, T.NAME AS [node!2!name]

FROM TREE T

WHERE T.ID = @ROOT_ID

UNION

SELECT 2, 1, NULL, C.ID, C.P_ID, C.NAME

FROM TREE P

INNER JOIN TREE C ON C.NLEFT BETWEEN P.NLEFT AND P.NRIGHT

WHERE P.ID = @ROOT_ID

FOR XML EXPLICIT





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE PROCEDURE TREE_RECALC(

	@ROOT_ID INT

)

AS



DECLARE @NLEFT INT, @NID INT, @NRIGHT INT



/* FIND THE PARENT NODE */

SELECT @NLEFT = NLEFT, @NID = @ROOT_ID 

FROM TREE

WHERE ID = @ROOT_ID AND P_ID IS NULL



EXEC TREE_RECALC_REC @NLEFT, @NID, 1, @NRIGHT OUTPUT



UPDATE TREE SET NLEFT = @NLEFT, NRIGHT = @NRIGHT, NLEVEL = 1 WHERE ID = @NID



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE PROCEDURE TREE_RECALC_ITER(

		@ROOT_ID INT

)

AS

/*

DECLARE @ROOT_ID INT

SET @ROOT_ID = 52

*/



DECLARE @ID INT

DECLARE @P_ID INT

DECLARE @NAME VARCHAR(100)

DECLARE @NLEVEL INT

DECLARE @NLEFT INT

DECLARE @NRIGHT INT



DECLARE @ID2 INT

DECLARE @P_ID2 INT

DECLARE @NAME2 VARCHAR(100)

DECLARE @NLEVEL2 INT

DECLARE @NLEFT2 INT

DECLARE @NRIGHT2 INT



SET @NLEVEL = 1

CREATE TABLE #TPC(ID INT, P_ID INT, NAME VARCHAR(100), NLEVEL INT)

CREATE TABLE #TCP(ID INT, P_ID INT, NAME VARCHAR(100), NLEVEL INT)

CREATE TABLE #T(ID INT, P_ID INT, NAME VARCHAR(100), NLEFT INT, NRIGHT INT, NLEVEL INT)

SELECT @P_ID = P_ID, @NAME = NAME, @NLEFT = NLEFT FROM TREE WHERE ID = @ROOT_ID

SET @NRIGHT = @NLEFT + 1

INSERT INTO #TPC VALUES(@ROOT_ID, @P_ID, @NAME, @NLEVEL)



WHILE @NLEVEL > 0

BEGIN

	IF EXISTS(SELECT * FROM #TPC WHERE NLEVEL = @NLEVEL)

	BEGIN

		SELECT TOP 1 @ID = ID, @P_ID = P_ID, @NAME = NAME FROM #TPC WHERE NLEVEL = @NLEVEL ORDER BY ID



		INSERT INTO #T VALUES(@ID, @P_ID, @NAME, @NLEFT, @NRIGHT, @NLEVEL)



		DELETE FROM #TPC WHERE NLEVEL = @NLEVEL AND ID = @ID



		INSERT INTO #TPC SELECT ID, P_ID, NAME, @NLEVEL + 1 FROM TREE WHERE P_ID = @ID

		IF @@ROWCOUNT > 0

			SET @NLEVEL = @NLEVEL + 1



		IF EXISTS(SELECT ID FROM TREE WHERE P_ID = @ID)

		BEGIN

			SET @NLEFT = @NLEFT + 1

			SET @NRIGHT = @NLEFT + 1

		END

		ELSE

		BEGIN

			IF @ID = (SELECT MAX(ID) FROM TREE WHERE NLEVEL = @NLEVEL AND P_ID = @P_ID)

			BEGIN

				PRINT LTRIM(RTRIM(STR(@ID))) + '	' + @NAME + '	' + LTRIM(RTRIM(STR(@NRIGHT)))

				SET @NLEVEL2 = 1

				TRUNCATE TABLE #TCP

				INSERT INTO #TCP SELECT ID, P_ID, NAME, @NLEVEL2 FROM #T WHERE ID = @P_ID

				WHILE @NLEVEL2 > 0

				BEGIN

					IF EXISTS(SELECT * FROM #TCP WHERE NLEVEL = @NLEVEL2)

					BEGIN

						SELECT TOP 1 @ID2 = ID, @P_ID2 = P_ID, @NAME2 = NAME FROM #TCP WHERE NLEVEL = @NLEVEL2 ORDER BY ID

						SET @NRIGHT = @NRIGHT + 1

						UPDATE #T SET NRIGHT = @NRIGHT WHERE ID = @ID2

						DELETE FROM #TCP WHERE NLEVEL = @NLEVEL2 AND ID = @ID2

						INSERT INTO #TCP SELECT ID, P_ID, NAME, @NLEVEL2 + 1 FROM #T WHERE ID = @P_ID2

						IF @@ROWCOUNT > 0

							SET @NLEVEL2 = @NLEVEL2 + 1

					END

					ELSE

					BEGIN

						SET @NLEVEL2 = @NLEVEL2 - 1

					END

				END



			END

			ELSE

			BEGIN

				SET @NLEFT = @NRIGHT + 1

				SET @NRIGHT = @NLEFT + 1

			END

		END



	END

	ELSE

	BEGIN

		SET @NLEVEL = @NLEVEL - 1

		SELECT @NLEFT = MAX(NRIGHT) + 1 FROM #T WHERE NLEVEL = @NLEVEL

		SET @NRIGHT = @NLEFT + 1

	END

END



DROP TABLE #TPC

DROP TABLE #TCP



--SELECT * FROM #T



UPDATE TREE SET NLEFT = #T.NLEFT, NRIGHT = #T.NRIGHT, NLEVEL = #T.NLEVEL

FROM TREE

INNER JOIN #T ON TREE.ID = #T.ID



DROP TABLE #T







GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





CREATE PROCEDURE TREE_RECALC_REC @NLEFT INT, @NPARENT INT, @NLEVEL INT, @NRIGHT INT OUTPUT

AS

DECLARE @NCOUNT INT, @NNEWLEFT INT, @NTEMPID INT, @NCOUNTER INT, @NLEVEL2 INT

/* COUNT THE NUMBER OF CHILDREN OF THE PARENT */

SELECT @NCOUNT = COUNT(*), @NLEVEL2 = @NLEVEL + 1

FROM TREE

WHERE P_ID = @NPARENT



/* START TO WALK DOWN THE TREE */

IF @NCOUNT > 0

BEGIN

	SELECT @NNEWLEFT = @NLEFT + 1

	SELECT @NTEMPID = 0

 

	/* FIND THE LOWEST ID FOR THE PARENT AND STORE ITS VALUE */

	SELECT @NTEMPID = MIN(ID), @NCOUNTER = COUNT(*) 

	FROM TREE 

	WHERE P_ID = @NPARENT AND ID > @NTEMPID

  

	/* LOOP THROUGH THESE UNTIL FINISHED */   

	WHILE (@NCOUNTER > 0)

	BEGIN

		/* RECURSE AND CALL AGAIN - WILL FINALLY DOP OUT AND FIND THE LAST RIGHT*/

		/* TO INCREMENT THE NEXT RIGHT                                          */

		EXEC TREE_RECALC_REC @NNEWLEFT, @NTEMPID, @NLEVEL2, @NRIGHT OUTPUT

		SELECT @NNEWLEFT = @NRIGHT + 1

		SELECT @NTEMPID = MIN(ID), @NCOUNTER = COUNT(*) 

		FROM TREE 

		WHERE P_ID = @NPARENT AND ID > @NTEMPID

	END

 

	/* NOW UPDATE THE NODE */

	UPDATE TREE SET NLEFT = @NLEFT, NRIGHT = @NRIGHT + 1, NLEVEL = @NLEVEL 

	WHERE ID = @NPARENT

	/* INCREMENT THE RIGHT COUNTER */

	SELECT @NRIGHT = @NRIGHT + 1

END

ELSE

	/* FINISHED THE BRANCH */

BEGIN

	SELECT @NRIGHT = @NLEFT + 1

	UPDATE TREE SET NLEFT = @NLEFT, NRIGHT = @NRIGHT, NLEVEL = @NLEVEL WHERE ID = @NPARENT

END





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE TRIGGER TREE_SET_LEFT_RIGHT ON dbo.TREE AFTER INSERT 

AS



DECLARE @ID INT

DECLARE @P_ID INT

DECLARE @MAX_VALUE INT

DECLARE @NLEVEL INT

DECLARE @ROOT_ID INT

DECLARE C_LEFT_RIGHT CURSOR LOCAL FOR SELECT ID, P_ID FROM inserted

OPEN C_LEFT_RIGHT

FETCH NEXT FROM C_LEFT_RIGHT INTO @ID, @P_ID

WHILE @@FETCH_STATUS = 0

BEGIN

	IF @P_ID IS NULL 

	BEGIN

		SELECT @MAX_VALUE = ISNULL(MAX(NLEFT), 0) + 10000 FROM TREE WHERE P_ID IS NULL

		SELECT @NLEVEL = 1

	END

	ELSE

	BEGIN

		SELECT @MAX_VALUE = ISNULL(MAX(NRIGHT), 0) FROM TREE WHERE P_ID = @P_ID AND ID < @ID

		SELECT @NLEVEL = ISNULL(NLEVEL, 0) FROM TREE WHERE P_ID = @P_ID AND ID < @ID

		IF @MAX_VALUE = 0

		BEGIN

			SELECT @MAX_VALUE = ISNULL(NLEFT, 0) FROM TREE WHERE ID = @P_ID

			SELECT @NLEVEL = ISNULL(NLEVEL, 0) + 1 FROM TREE WHERE ID = @P_ID

		END

	END

--	PRINT STR(LTRIM(RTRIM(@MAX_VALUE)))

	UPDATE TREE SET NLEFT = @MAX_VALUE + 1, NRIGHT = @MAX_VALUE + 2, NLEVEL = @NLEVEL WHERE ID = @ID

	IF @P_ID IS NOT NULL

	BEGIN

		SELECT @ROOT_ID = ISNULL(P.ID, -1)

		FROM TREE C 

		INNER JOIN TREE P ON C.NLEFT BETWEEN P.NLEFT AND P.NRIGHT

		WHERE C.ID = @P_ID AND P.P_ID IS NULL

--		EXEC TREE_RECALC @ROOT_ID -- RECURSIVE APPROACH

		EXEC TREE_RECALC_ITER @ROOT_ID -- ITERATIVE APPROACH

	END

	FETCH NEXT FROM C_LEFT_RIGHT INTO @ID, @P_ID

END

CLOSE C_LEFT_RIGHT

DEALLOCATE C_LEFT_RIGHT



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO