I just added the Sample data..

Do you want the code for Triggers?

The triggers only fire on Update of hirNodeParent column and Title column and I am only updating the full path column and anyway I have disabled the triggers for now.

I am attaching the code for indexes but for now I have deleted them too

/****** Object: Index [idx_hirNodes_fullPath] Script Date: 10/31/2012 19:39:16 ******/

CREATE NONCLUSTERED INDEX [idx_hirNodes_fullPath] ON [dbo].[hirNodes]

(

[FullPath] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

/****** Object: Index [idx_hirNodes_hirLevel] Script Date: 10/31/2012 19:39:52 ******/

CREATE NONCLUSTERED INDEX [idx_hirNodes_hirLevel] ON [dbo].[hirNodes]

(

[hirLevel] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

/****** Object: Index [idx_hirNodes_hirNodeParent] Script Date: 10/31/2012 19:41:47 ******/

CREATE NONCLUSTERED INDEX [idx_hirNodes_hirNodeParent] ON [dbo].[hirNodes]

(

[hirNodeParent] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

/****** Object: Index [idx_hirNodes_Title] Script Date: 10/31/2012 19:47:55 ******/

CREATE NONCLUSTERED INDEX [idx_hirNodes_Title] ON [dbo].[hirNodes]

(

[Title] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO