Parent Child index?

I have a table that has recursive relationship with itself. The primary key
is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier). I
would like to create an index for these two fields, including cascading
deletes (regardless of the number of decendants). Is this possible? If so,
can someone step me thru it?
Thanks,
Craig BuchananHi
You can't create a FK with cascading deletes when the FK refers to the same
table. If you implemented this with a trigger you would be restricted to 32
levels. What you may want to do is to run a batch process that clears up the
table.
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message
news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> I have a table that has recursive relationship with itself. The primary
key
> is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier).
I
> would like to create an index for these two fields, including cascading
> deletes (regardless of the number of decendants). Is this possible? If
so,
> can someone step me thru it?
> Thanks,
> Craig Buchanan
>|||John-
How would I create a trigger between two tables where PK and FK are
uniqueidentifiers?
Thanks,
Craig
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> Hi
> You can't create a FK with cascading deletes when the FK refers to the
same
> table. If you implemented this with a trigger you would be restricted to
32
> levels. What you may want to do is to run a batch process that clears up
the
> table.
> John
> "Craig Buchanan" <someone@.microsoft.com> wrote in message
> news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> key
(uniqueidentifier).
> I
> so,
>|||Hi Craig
Something like:
CREATE TABLE ParentChildTable ( id int not null CONSTRAINT
PK_ParentChild PRIMARY KEY,
parentid int not null )
DROP TABLE ParentChildTable
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
DELETE FROM ParentChildTable
FROM ParentChildTable P JOIN DELETED D ON P.Parentid = d.id
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
sp_configure 'nested triggers'
/*
Needs to be 1
name minimum maximum
config_value run_value
-- -- --
-- --
nested triggers 0 1 1
1
*/
sp_dboption testdb, 'recursive triggers'
/*
Needs to be ON
OptionName CurrentSetting
-- --
recursive triggers ON
*/
DELETE FROM ParentChildTable WHERE id = 3
/* id parentid
-- --
3 2
(1 row(s) affected)
id parentid
-- --
4 3
(1 row(s) affected)
id parentid
-- --
5 4
(1 row(s) affected)
id parentid
-- --
6 5
(1 row(s) affected)
id parentid
-- --
7 6
(1 row(s) affected)
id parentid
-- --
8 7
(1 row(s) affected)
id parentid
-- --
9 8
(1 row(s) affected)
id parentid
-- --
10 9
(1 row(s) affected)
id parentid
-- --
11 10
(1 row(s) affected)
id parentid
-- --
12 11
(1 row(s) affected)
id parentid
-- --
13 12
(1 row(s) affected)
id parentid
-- --
14 13
(1 row(s) affected)
id parentid
-- --
15 14
(1 row(s) affected)
id parentid
-- --
16 15
(1 row(s) affected)
id parentid
-- --
17 16
(1 row(s) affected)
id parentid
-- --
18 17
(1 row(s) affected)
id parentid
-- --
19 18
(1 row(s) affected)
id parentid
-- --
20 19
(1 row(s) affected)
id parentid
-- --
21 20
(1 row(s) affected)
id parentid
-- --
22 21
(1 row(s) affected)
id parentid
-- --
23 22
(1 row(s) affected)
id parentid
-- --
24 23
(1 row(s) affected)
id parentid
-- --
25 24
(1 row(s) affected)
id parentid
-- --
26 25
(1 row(s) affected)
id parentid
-- --
27 26
(1 row(s) affected)
id parentid
-- --
28 27
(1 row(s) affected)
id parentid
-- --
29 28
(1 row(s) affected)
id parentid
-- --
30 29
(1 row(s) affected)
id parentid
-- --
31 30
(1 row(s) affected)
id parentid
-- --
32 31
(1 row(s) affected)
id parentid
-- --
33 32
(1 row(s) affected)
id parentid
-- --
34 33
(1 row(s) affected)
Server: Msg 217, Level 16, State 1, Procedure TRG_ParentChildTable,
Line 7
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
*/
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message news:<eOG$Y6BEEHA.2404@.TK2MSFTNGP
11.phx.gbl>...
> John-
> How would I create a trigger between two tables where PK and FK are
> uniqueidentifiers?
> Thanks,
> Craig
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> same
> 32
> the
> key
> (uniqueidentifier).
> I
> so,|||Hi Craig
This may be an alternative, but if it may cause contention on this table and
you may be better off running a scheduled task to do the deletions in a qui
et period.
sp_dboption testdb, 'recursive triggers', false
sp_dboption testdb, 'recursive triggers'
/*
Needs to be off
OptionName CurrentSetting
-- --
recursive triggers off
*/
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
WHILE @.@.ROWCOUNT > 0
DELETE FROM ParentChildTable
FROM ParentChildTable
WHERE ParentId <> 0
AND ParentId NOT IN ( SELECT Id FROM ParentChildTable )
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
DELETE FROM ParentChildTable WHERE Id = 3
SELECT * FROM ParentChildTable
John