relationship inside the same table

i ve got a database that has a table...that table has a relationship between its primary key,and another field,
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)