Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version

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

, 19 Jul 2006 CPOL
This article describes how to efficiently store and access tree structures in a SQL Server database, and how to use them in OLAP implementations.
refreshsalescube.zip
RefreshSalesCube
App.ico
bin
Release
Interop.DSO.dll
Interop.MSOLAPADMINLib2.dll
Interop.POMInterfaces.dll
Interop.VBA.dll
RefreshSalesCube.exe
RefreshSalesCube.csproj.user
sales.zip
sqlscripts.zip
tree_util.zip
tree_util
bin
tree_util.dll
Global.asax
tree.xsl
tree_util.csproj.webinfo
��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



By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dan Radu
Web Developer Telstra Internet
Australia Australia
I live and work in Bucharest, Romania. I am programmer since 1998, when I have developed a "good taste" application for a catering company. Now I develop .NET applications (windows and ASP.NET) for large SQL Server database systems, with tens of millions of records.
I like to develop also in other languages like Object Pascal (Delphi), PHP, C++, VB, scripting. I enjoy the XML power, both on client side and server side.

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 19 Jul 2006
Article Copyright 2006 by Dan Radu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid