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 ;)

regular expression issue

I am trying to exclude all strings that has 'a' inside (I have
simplified the actual problem)
select 1
where 'bb b a dfg' like '%[^a]%'

However, the above does not work. By the way, I can not use 'not like'
such as:

select 1
where 'bb b a dfg' not like '%a%'
Although the above will work but the idea is that I have to use 'like'
and not 'not like'. This is partly because I have to exclude rows from
an exclusion table (a table that has many rows that will be excluded).
Actually I want to include all srings that has lets say // in it using
a regular expression. I would like to write it as (I am sure it will
not work):
select 1
where column like '%[^/][^/]%'

That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etc
and include strings like: 'aaa aa aa' or 'bb aa nn' etc

Is there any way to write a regular expression to do it? Otherrwise I
have to solve this problem without using regular expressions in the
exclusion table.
Thanks.The problem you pose requires NOT LIKE. There is no way to express a
LIKE string with the "not" test inside that will do what was
specified. The NOT has to be outside.

Roy Harvey
Beacon Falls, CT

On 7 Nov 2006 21:07:01 -0800, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

>I am trying to exclude all strings that has 'a' inside (I have
>simplified the actual problem)
>select 1
>where 'bb b a dfg' like '%[^a]%'
>
>However, the above does not work. By the way, I can not use 'not like'
>such as:
>
>select 1
>where 'bb b a dfg' not like '%a%'
>Although the above will work but the idea is that I have to use 'like'
>and not 'not like'. This is partly because I have to exclude rows from
>an exclusion table (a table that has many rows that will be excluded).
>Actually I want to include all srings that has lets say // in it using
>a regular expression. I would like to write it as (I am sure it will
>not work):
>select 1
>where column like '%[^/][^/]%'
>
>That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etc
>and include strings like: 'aaa aa aa' or 'bb aa nn' etc
>
>Is there any way to write a regular expression to do it? Otherrwise I
>have to solve this problem without using regular expressions in the
>exclusion table.
>Thanks.