Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 2K5 Cascading Delete on Recursive Foreign Key

Author  Topic 

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-24 : 10:15:07
In SQL Server 2005, I have a self-referencing table that has a primary key field of Employee_ID and another field called Supervisor_ID, which acts as a foreign key pointing to the primary key. In essence this table holds aparent-child structure. I am able to create the foreign key dependency, but am unable to set up a cascading rule to DELETE all descendant records when a record is deleted. When setting up the relationship in SQL Server 2005, the "INSERT and UPDATE Specification" section is grayed out. When creating foreign keys relationships in multiple tables, I am able to do this, but it seems that SQL Server does not allow it for self-referencing tables. Does anyone know how I might accomplish this? In general, when I delete rox X, I need to also delete all the descendant rows. Thanks,

Al

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 10:50:03
You could write a trigger to do the delete for you, recursively.

Kristen
Go to Top of Page

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-24 : 12:14:33
I understand how to create a trigger, but how could one be made to do the delete recursively?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 12:40:06
Somthing like this:

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyEmployeeTable
AFTER DELETE
AS
SET NOCOUNT ON
DECLARE @MyTempTable TABLE
(
T_Employee_ID int NOT NULL,
PRIMARY KEY
(
Employee_ID
)
)

-- Prepopulate with Employee numbers of all record(s) being deleted
INSERT INTO @MyTempTable(T_Employee_ID)
SELECT D.Employee_ID
FROM deleted as D
LEFT OUTER JOIN inserted AS I
ON I.Employee_ID = D.Employee_ID
WHERE I.Employee_ID

DECLARE @intRowCount int
SELECT @intRowCount = 1 -- Force first iteration

WHILE @intRowCount > 0
BEGIN
-- Recursively populate with all Supervisees
INSERT INTO @MyTableTable(T_Employee_ID)
SELECT DISTINCT E.Employee_ID
FROM @MyTableTable AS T1
-- All Employess who have a supervisor in the list
JOIN dbo.MyEmployeeTable AS E
ON E.Supervisor_ID = T1.T_Employee_ID
-- But check they are not already in the list
LEFT OUTER JOIN @MyTableTable AS T2
ON T2.T_Employee_ID = E.Employee_ID
WHERE T2.T_Employee_ID IS NULL -- Only if not already present
SELECT @intRowCount = @@ROWCOUNT
END

-- Delete all emloyees located as decendents of supervisor
DELETE E
FROM dbo.MyEmployeeTable AS E
JOIN @MyTableTable
ON T_Employee_ID = E.Employee_ID
GO

Kristen
Go to Top of Page

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-24 : 20:17:19
Thanks for your help. I have created the script and have gotten it to execute. One question, though that you might be able to answer. I know the trigger was created because I tried running the script a second time and I got a message that stated the trigger already existed. Now I can only run ALTER scripts. However, I can't seem to find the trigger in the object explorer in SQL Server Management Studio. The Programbility...Database Triggers folder is empty. Do you know where tigger's show up ithe object explorer?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 23:08:28
Sorry, not used SQL 2005 or SSMS

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-24 : 23:27:35
it's under the
tables>>tablename>>triggers

--------------------
keeping it simple...
Go to Top of Page

akashenk
Posting Yak Master

111 Posts

Posted - 2006-07-25 : 09:29:38
Ahhh.. that makes sense. Thanks for all your help!
Go to Top of Page
   

- Advertisement -