self-joined table & recursive cascade deletion

[stevenba]

Hi,

************************************************** *****
* Main problem - cascade deletion of self joins and also child tables. ****
************************************************** *****

I have a schema with a self join in the main table as well as other child tables. Its for a CRM with a recursive data structure where a contact can be a parent of another contact, eg group->company->dept->sales manager.

I prototyped in ms access and thought it would be straight forward to upsize to SQL Server, but how naive I was. I'm newish to SQL server but have done this before in Postgres using 'fk REFERENCES pk on CASCADE DELETE' type of DDL.

I have tried using DTS which doesn't seem to import relationships or RI. I have tried upsizing from MS Access XP using both DRI and triggers, but have problems with both.

Because i plan on replicating the db in the future, i am using GUIDs as PKs throughout.

One immediate problem on upsizing was that any GUID fields were defaulting to NewGuid, which is breaking the RI, because these fk GUIDs were trying to reference a PK which it had just generated a value for. I manually fixed that, by defaulting to NULL and changed the insert trigger to ignore NULLS.

My biggest problem is getting cascade deletions working on the self referenced table. It won't seem to touch it with DRI, but works partially with triggers.

Should i forget DTS and Ms Access upsizing and code the DDL myself, or am I pretty close?

Thanks in advance for any tips ...

AN incomplete DDL script of the table is below:

************** START DDL CONTACTS TABLE *******************
CREATE TABLE [dbo].[Contacts] (
[ContactID] [uniqueidentifier] NOT NULL ,
[CreatorName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ParentID] [uniqueidentifier] NULL ,
[ContactType] [uniqueidentifier] NULL ,
[SourceOfInquiry] [uniqueidentifier] NULL ,
[soundex_lastname] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[soundex_displayname] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contacts] ADD
CONSTRAINT [DF__Contacts__Contac__286302EC] DEFAULT (newid()) FOR [ContactID],
CONSTRAINT [DF__Contacts__Creato__29572725] DEFAULT ('ADMIN') FOR [CreatorName],
CONSTRAINT [DF__Contacts__Parent__2A4B4B5E] DEFAULT (null) FOR [ParentID],
CONSTRAINT [DF__Contacts__Contac__2B3F6F97] DEFAULT (null) FOR [ContactType],
CONSTRAINT [DF__Contacts__Source__2F10007B] DEFAULT (null) FOR [SourceOfInquiry],
CONSTRAINT [aaaaaContacts_PK] PRIMARY KEY NONCLUSTERED
(
[ContactID]
) ON [PRIMARY]
GO

CREATE INDEX [{8CE0F842-5BE0-4860-917D-D2BC0B9C2D50}] ON [dbo].[Contacts]([ParentID]) ON [PRIMARY]
GO

CREATE INDEX [CampaignID] ON [dbo].[Contacts]([SourceOfInquiry]) ON [PRIMARY]
GO

CREATE INDEX [soundex_displayname] ON [dbo].[Contacts]([soundex_displayname]) ON [PRIMARY]
GO

CREATE INDEX [soundex_lastname] ON [dbo].[Contacts]([soundex_lastname]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contacts] ADD
CONSTRAINT [Contacts_FK00] FOREIGN KEY
(
[ParentID]
) REFERENCES [dbo].[Contacts] (
[ContactID]
),
CONSTRAINT [Contacts_FK01] FOREIGN KEY
(
[ContactType]
) REFERENCES [dbo].[ContactTypes] (
[ID]
),
CONSTRAINT [Contacts_FK02] FOREIGN KEY
(
[SourceOfInquiry]
) REFERENCES [dbo].[Campaigns] (
[ID]
)
GO

alter table [dbo].[Contacts] nocheck constraint [Contacts_FK00]
GO

alter table [dbo].[Contacts] nocheck constraint [Contacts_FK01]
GO

alter table [dbo].[Contacts] nocheck constraint [Contacts_FK02]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER T_Contacts_ITrig
ON Contacts FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Contacts' */
IF (SELECT ParentID FROM inserted) != NULL
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Contacts, inserted WHERE (Contacts.ContactID = inserted.ParentID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''Contacts''.'
ROLLBACK TRANSACTION
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER T_Contacts_DTrig
ON Contacts FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'Opportunities' */
DELETE Opportunities FROM deleted, Opportunities WHERE deleted.ContactID = Opportunities.ContactID

/* * CASCADE DELETES TO 'Journal' */
DELETE Journal FROM deleted, Journal WHERE deleted.ContactID = Journal.ContactID

/* * CASCADE DELETES TO 'GroupMembers' */
DELETE GroupMembers FROM deleted, GroupMembers WHERE deleted.ContactID = GroupMembers.ContactID

/* * CASCADE DELETES TO 'ContactActivities' */
DELETE ContactActivities FROM deleted, ContactActivities WHERE deleted.ContactID = ContactActivities.ContactID

/* * CASCADE DELETES TO 'support_tickets' */
DELETE support_tickets FROM deleted, support_tickets WHERE deleted.ContactID = support_tickets.contact_id

/* * CASCADE DELETES TO 'ContactNotes' */
DELETE ContactNotes FROM deleted, ContactNotes WHERE deleted.ContactID = ContactNotes.ContactID

/* * CASCADE DELETES TO 'Documents' */
DELETE Documents FROM deleted, Documents WHERE deleted.ContactID = Documents.ContactID

/* * CASCADE DELETES TO 'Items' */
DELETE Items FROM deleted, Items WHERE deleted.ContactID = Items.ContactID

/* * RECURSIVE CASCADE DELETES TO 'Contacts' */
DELETE Contacts FROM deleted, Contacts WHERE deleted.ContactID = Contacts.ParentID

***************** END DDL CONTACTS TABLE *******************

 

[vongrunt]

Is database configured for recursive firing of triggers (sp_dboption)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stevenba]

I have tried it both on and off. I would have assumed it work ok with it on, but not.

btw, used exec db_option 'mydbname', 'recursive triggers', called from query analyser.

.. to (un)set and check this. Is there anything i'm missing like resarting server or anything.

thanks for the prompt respone btw ...

 

[stevenba]

Have set recursive triggers to on , but when i try a cascade delete, i get an error:

[microsoft][odbc sql server driver][sql server]Maximum stored procedure,function, trigger, or view nesting level exceeded (limit 32).

Surely it can't be that difficult to do?

Anyway, i will be away from my PC from now until Mon(1st Aug), about 5am GMT, so will view/reply to any posts then.

Thanx in advance.

 

[vongrunt]

I'm 99.9999995% sure you don't have a parent-child hierarchy with 32+ levels so...

Personally I'd check first for anomalies in existing data. Perhaps there is some kind of cyclical reference that causes infinite firing of triggers. For example:

A is parent of B
B is parent of C
C is parent of A

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stevenba]

Thanks for the reply.

I'm absolutely sure there is no cyclical ref. This is enforced in the application, and when I delete the exactly same data in MS Access, this happens with no problems.

I will look to rewriting the ddl created when MS Access upsized the schema.

 

[stevenba]

Have used DTS to import just the one table with no ref integrity or triggers.

Created PK on ContactID, and then used this DDL SQL to attempt the self join (ParentID -> ContactID).


ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [Contacts_FK00] FOREIGN KEY
(
[ParentID]
) REFERENCES [dbo].[Contacts](
[ContactID]
) ON DELETE CASCADE;

The error I received is shown below.

Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'Contacts_FK00' on table 'Contacts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

I have done this in PostGres before with no trouble at all. Is there something I'm missing with SQL Server ???

 

[vongrunt]

SQL Server won't allow potentially cyclical cascaded DRI. Same thing happens on many tables, when all keys in a loop point to same direction (clockwise or counter-clockwise).

IMO triggers and/or stored procedures are the only options here. Do you have some sample data to play with?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stevenba]

Thanks for the reply. I can give you sample data. How do you want it? I assume a text dump of table def and data would be OK?

I'm not sure how to dump the data in SQL Server but will take a look.

Thanx,

Steve Baker

 

[vongrunt]

OK. Post data only from relevant columns - say, ContactID, ParentID and DisplayName. Simple copy & paste results from QA - that should be enough.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stevenba]

The DDL to create the sample table is below:

************* start of create table ************************
CREATE TABLE [Contacts_Sample_Data] (
[ContactID] [uniqueidentifier] NOT NULL ,
[CreatorName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ParentID] [uniqueidentifier] NULL ,
PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contacts_Sample_Data_Contacts_Sample_Data] FOREIGN KEY
(
[ParentID]
) REFERENCES [Contacts_Sample_Data] (
[ContactID]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

*************** end of create table ************************

Will post data very soon ...

 

[stevenba]

The data in CSV format is below:

************* start of data ************************
B02D292E-B5DE-4C92-A45E-0354A85401FB,"sbaker","Borg, Simon",NULL
B07381A7-C103-4178-AF40-089793EBB169,"sbaker","Baker, Gary",NULL
DA8DF60A-EA57-4463-98A7-1AF2A4A351C7,"sbaker",Co-Op garage,NULL
E7853E4E-02DE-45AE-919B-21C4C4A4709A,"sbaker","Stewart, Ray",ABECF535-5563-4F5F-8F46-38E255B0AF7A
56445E54-4D3B-47D0-BC7F-227DD8251B34,"sbaker","Webb, Michell",A615D96E-7E03-43E9-A583-F2047850F301
9BE9E639-E7C3-41AF-BF5A-2D7459BCDEB1,"sbaker","Gilbert, Vivien",NULL
85B9B7F8-A706-4818-AD54-2EE1C1A1FE0C,"sbaker","Farley, Guy",ECC2944E-D46B-45CF-BE97-DD19ACA1D9EB
ABECF535-5563-4F5F-8F46-38E255B0AF7A,"sbaker",Business Solutions,NULL
0A69BFFC-9602-41E9-ACE4-39CBD98E8AEB,"sbaker","Manning, Luke",1C5A52E2-1BD5-423E-87B5-8B679FEFFE69
C17EF6EF-3586-488E-88A0-3DA0CAE140C0,"sbaker","Smurthwaite, Mick",NULL
657D6747-3CDC-47CB-BA57-5B4C1C347294,"sbaker","Baker, Eve",NULL
9C190688-6F62-4E58-8928-5D4DF8960954,"sbaker",Temple CMT,NULL
2DE7283C-3E25-4BC1-A8DE-653579BF423A,"sbaker",Telewest,NULL
1BFEAB25-D550-4DDD-BB18-76D45ADFA3C2,"sbaker",", Dimitris",NULL
2C449D23-95E7-44ED-9B2C-79EC8249EC32,"sbaker",Spell Software,NULL
2C43BD5E-6C07-44A9-A573-7BAC54ECCA1B,"sbaker",", Soniah",2C449D23-95E7-44ED-9B2C-79EC8249EC32
5578221A-F7C4-4EAB-BF9E-8410C2A7B804,"sbaker",", Bally",A615D96E-7E03-43E9-A583-F2047850F301
393C5762-955F-4F89-AD8C-87A4544AD2B7,"sbaker","Westway, Steve",9C190688-6F62-4E58-8928-5D4DF8960954
1C5A52E2-1BD5-423E-87B5-8B679FEFFE69,"sbaker",Learning Fee International,NULL
134AAE75-C731-4D7B-BE45-91368B173BFE,"sbaker",", ",DA8DF60A-EA57-4463-98A7-1AF2A4A351C7
7B270C62-7470-49EE-93CE-A5A0AAC863A1,"sbaker","Pearce, Dave",NULL
B5D75361-97FC-438B-979B-B13FA05A50F7,"sbaker","Randall, Tony",NULL
B5DDC8B6-1C3E-449E-BD86-B15C9134A35D,"sbaker","Ray, Fiona",NULL
5EAE5B57-70DD-4473-82A2-C1D46BFD58D6,"sbaker","Business, Bt",NULL
8BFD46FA-D81D-4798-A639-C708CFAF3BA0,"sbaker","Swain, Mark",NULL
F3C81D6F-19F3-474D-B79E-CCF62597447E,"sbaker","Ed, Troy",F7E9CD8C-AC84-4AB6-99EA-E4BAFEFAC662
828B0405-10CA-4A82-A97C-D5FD9C72F1A8,"sbaker",BaKare,NULL
78C5FA1C-7155-4135-8013-D90A28A28EDF,"sbaker",", Overture",NULL
47DAAF98-E1D2-4F34-B1EA-DB86A5FCBE62,"sbaker","Kapoor, Ajay",2DE7283C-3E25-4BC1-A8DE-653579BF423A
4B008597-34EB-42DC-BBBA-DC5FCF5DB22E,"sbaker","Mahoney, Ivan",NULL
ECC2944E-D46B-45CF-BE97-DD19ACA1D9EB,"sbaker",Guy Farley & Co,NULL
F7E9CD8C-AC84-4AB6-99EA-E4BAFEFAC662,"sbaker",Computer Cabin,NULL
D7EDAC9C-FDBF-40DA-9177-E7816A562A94,"sbaker","Baker, John",828B0405-10CA-4A82-A97C-D5FD9C72F1A8
09E6803F-4477-4824-9311-E97149708C5C,"sbaker","Willard, Jane",NULL
A54C9233-5390-473B-8A08-F046BD0EB510,"sbaker","Support, Telewest",NULL
A615D96E-7E03-43E9-A583-F2047850F301,"sbaker",Eurobell,NULL
12B2496D-99E9-4779-8F2E-FCB068D586AD,"sbaker","Fearon, Sean",NULL
************* end of data ************************

 

[stevenba]

Apl;ogoes - some of the delimiters did not seem to appear. The corrected version is below.

The data in CSV format is below:

************* start of data ************************
B02D292E-B5DE-4C92-A45E-0354A85401FB,"sbaker","Borg, Simon",NULL
B07381A7-C103-4178-AF40-089793EBB169,"sbaker","Baker, Gary",NULL
DA8DF60A-EA57-4463-98A7-1AF2A4A351C7,"sbaker","Co-Op garage",NULL
E7853E4E-02DE-45AE-919B-21C4C4A4709A,"sbaker","Stewart, Ray",ABECF535-5563-4F5F-8F46-38E255B0AF7A
56445E54-4D3B-47D0-BC7F-227DD8251B34,"sbaker","Webb, Michell",A615D96E-7E03-43E9-A583-F2047850F301
9BE9E639-E7C3-41AF-BF5A-2D7459BCDEB1,"sbaker","Gilbert, Vivien",NULL
85B9B7F8-A706-4818-AD54-2EE1C1A1FE0C,"sbaker","Farley, Guy",ECC2944E-D46B-45CF-BE97-DD19ACA1D9EB
ABECF535-5563-4F5F-8F46-38E255B0AF7A,"sbaker","Business Solutions",NULL
0A69BFFC-9602-41E9-ACE4-39CBD98E8AEB,"sbaker","Manning, Luke",1C5A52E2-1BD5-423E-87B5-8B679FEFFE69
C17EF6EF-3586-488E-88A0-3DA0CAE140C0,"sbaker","Smurthwaite, Mick",NULL
657D6747-3CDC-47CB-BA57-5B4C1C347294,"sbaker","Baker, Eve",NULL
9C190688-6F62-4E58-8928-5D4DF8960954,"sbaker","Temple CMT",NULL
2DE7283C-3E25-4BC1-A8DE-653579BF423A,"sbaker","Telewest",NULL
1BFEAB25-D550-4DDD-BB18-76D45ADFA3C2,"sbaker",", Dimitris",NULL
2C449D23-95E7-44ED-9B2C-79EC8249EC32,"sbaker","Spell Software",NULL
2C43BD5E-6C07-44A9-A573-7BAC54ECCA1B,"sbaker",", Soniah",2C449D23-95E7-44ED-9B2C-79EC8249EC32
5578221A-F7C4-4EAB-BF9E-8410C2A7B804,"sbaker",", Bally",A615D96E-7E03-43E9-A583-F2047850F301
393C5762-955F-4F89-AD8C-87A4544AD2B7,"sbaker","Westway, Steve",9C190688-6F62-4E58-8928-5D4DF8960954
1C5A52E2-1BD5-423E-87B5-8B679FEFFE69,"sbaker","Learning Tree International",NULL
134AAE75-C731-4D7B-BE45-91368B173BFE,"sbaker",", ",DA8DF60A-EA57-4463-98A7-1AF2A4A351C7
7B270C62-7470-49EE-93CE-A5A0AAC863A1,"sbaker","Pearce, Dave",NULL
B5D75361-97FC-438B-979B-B13FA05A50F7,"sbaker","Randall, Tony",NULL
B5DDC8B6-1C3E-449E-BD86-B15C9134A35D,"sbaker","Ray, Fiona",NULL
5EAE5B57-70DD-4473-82A2-C1D46BFD58D6,"sbaker","Business, Bt",NULL
8BFD46FA-D81D-4798-A639-C708CFAF3BA0,"sbaker","Swain, Mark",NULL
F3C81D6F-19F3-474D-B79E-CCF62597447E,"sbaker","Ed, Troy",F7E9CD8C-AC84-4AB6-99EA-E4BAFEFAC662
828B0405-10CA-4A82-A97C-D5FD9C72F1A8,"sbaker","BaKare",NULL
78C5FA1C-7155-4135-8013-D90A28A28EDF,"sbaker",", Overture",NULL
47DAAF98-E1D2-4F34-B1EA-DB86A5FCBE62,"sbaker","Kapoor, Ajay",2DE7283C-3E25-4BC1-A8DE-653579BF423A
4B008597-34EB-42DC-BBBA-DC5FCF5DB22E,"sbaker","Mahoney, Ivan",NULL
ECC2944E-D46B-45CF-BE97-DD19ACA1D9EB,"sbaker","Guy Farley & Co",NULL
F7E9CD8C-AC84-4AB6-99EA-E4BAFEFAC662,"sbaker","Computer Cabin",NULL
D7EDAC9C-FDBF-40DA-9177-E7816A562A94,"sbaker","Baker, John",828B0405-10CA-4A82-A97C-D5FD9C72F1A8
09E6803F-4477-4824-9311-E97149708C5C,"sbaker","Willard, Jane",NULL
A54C9233-5390-473B-8A08-F046BD0EB510,"sbaker","Support, Telewest",NULL
A615D96E-7E03-43E9-A583-F2047850F301,"sbaker","Eurobell",NULL
12B2496D-99E9-4779-8F2E-FCB068D586AD,"sbaker","Kearon, Sean",NULL

************* end of data ************************

 

[stevenba]

I beleive that I have fixed it.

I have created a recursive procedure which drills down through the heirarchy deleting children by calling itself. This is initially triggered by an on delete trigger set for the contacts table. However, to get this to work, the options for recursive triggers has to be set to OFF. This is to let the recursive proc handle the cascade deletion by being called once only from the initial deletion firing the trigger.

The recursive proc is shown below:


******************* start proc *************************
CREATE PROCEDURE [sp_delete_Contacts]
(@ContactID_IN varchar(255))
AS
DECLARE @ChildID varchar(255)
/* local cursor, because recursive function */
DECLARE cur_level CURSOR LOCAL FOR

/* query to get children */
SELECT contactID FROM [dbo].[Contacts] WHERE [ParentID] = @ContactID_IN

/* open local cursor */
OPEN cur_level
FETCH NEXT FROM cur_level INTO @ChildID
/* while not end of file, iterate through child keys */
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ChildID <> '' AND @ChildID IS NOT NULL
BEGIN
/* recursive call to perform op on children */
exec sp_delete_Contacts @ChildID
END

FETCH NEXT FROM cur_level INTO @ChildID
END

CLOSE cur_level
DEALLOCATE cur_level

/* delete this contact */
DELETE FROM [CRM-DTS-TEST].[dbo].[Contacts]
WHERE ([ContactID] = @ContactID_IN)

GO
******************* end proc *************************


The trigger to call this is shown below:

******************* start trigger *************************
CREATE TRIGGER T_Contacts_DTrig
ON Contacts FOR DELETE AS
SET NOCOUNT ON
/* * RECURSIVE CASCADE DELETES FOR SELF JOIN */
DECLARE @ID varchar(255)

/* local cursor, because calling recursive function which uses cursors */
DECLARE cur_level CURSOR LOCAL FOR
SELECT ContactID FROM deleted

/* open local cursor */
OPEN cur_level
FETCH NEXT FROM cur_level INTO @ID
/* while not end of file, iterate through all contacts awaiting deletion */
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ID <> '' AND @ID IS NOT NULL
BEGIN
/* call procedure */
EXECUTE sp_delete_contacts @ID
END

FETCH NEXT FROM cur_level INTO @ID
END

CLOSE cur_level
DEALLOCATE cur_level

******************* end trigger *************************

If anybody has any thoughts on this, Im keen to know - it seems OK after initial tests.

 

[vongrunt]

This worked for me (recursive triggers enabled, NOCHECK on foreign key):

alter trigger T_contacts_Dtrig on Contacts_Sample_Data
for delete
as
[b]if exists( select * from deleted )[/b]
	delete from Contacts_Sample_Data where ParentID in (select ContactID from deleted)
go

Weird, I know...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stevenba]

Thanks for that.

It works great for the table in isolation. But with other tables 'hanging off it' ie having fk relate to the contacts pk, when deleting, i get the error '[microsoft][odbc sql server driver][sql server]Maximum stored procedure,function, trigger, or view nesting level exceeded (limit 32).'

Thats with using DRI instead of triggers to cascade delete the other tables.