Trigger/constraints/ stored procedure for a tree

We have 2 tables : S S_S S contains sites (a geographical location, a building, a city, a company...) Sites are linked together in a table S_S A record in S_S indicates ASId is linked to BSid Links should be entered only once We need two TRANSAC Sql procedures : a) For a given site in the network, and a given distance d, we want to have the list of the sites 'connected' to the site SId and with maximum distance d from SId. This list must be in a recordset format (select Sid from xxx) and not in a string format (SId1, Sid2...). The distance is the number of levelsthat separate two elements in the tree b) A set of constraints/ triggers in the table S_S to prevent entering multiple entries and 'tree illegal entries' Illegal tree entries are those groups of entries that will result in a loop. For example A linked to B B linked to C C may not be linked to A as it is already linked via B Suggested solution : have all the records S_S in the form of a regular tree There will be necessarily a top parent, whoc can have one or several children etc...I assume this is the only simple solution The tables are scripted below There is a record zero in S (Sid = 0, Sname = 'NA' ) that allows to have the constraint Not null on ASid and BSid. This record is simply ignored in the result. The constraint 'Not null' in fields must be kept. CREATE TABLE [dbo].[S] ( [SId] [int] IDENTITY (1, 1) NOT NULL , [SName] [nvarchar] (100) COLLATE French_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[S_S] ( [SSId] [int] IDENTITY (1, 1) NOT NULL , [ASId] [int] NOT NULL , [BSId] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[S] WITH NOCHECK ADD CONSTRAINT [PK_S] PRIMARY KEY CLUSTERED ( [SId] ) ON [PRIMARY] GO ALTER TABLE [dbo].[S_S] WITH NOCHECK ADD CONSTRAINT [PK_S_S] PRIMARY KEY CLUSTERED ( [SSId] ) ON [PRIMARY] GO ALTER TABLE [dbo].[S_S] ADD CONSTRAINT [FK_S_S_S] FOREIGN KEY ( [ASId] ) REFERENCES [dbo].[S] ( [SId] ), CONSTRAINT [FK_S_S_S1] FOREIGN KEY ( [BSId] ) REFERENCES [dbo].[S] ( [SId] ) GO ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request. 3) Complete ownership and distribution copyrights to all work purchased. ## Platform sql server 2000