SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 2K5 Cascading Delete on Recursive Foreign Key
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

akashenk
Posting Yak Master

111 Posts

Posted - 07/24/2006 :  10:15:07  Show Profile
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

United Kingdom
22415 Posts

Posted - 07/24/2006 :  10:50:03  Show Profile
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 - 07/24/2006 :  12:14:33  Show Profile
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

United Kingdom
22415 Posts

Posted - 07/24/2006 :  12:40:06  Show Profile
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

Edited by - Kristen on 07/24/2006 12:45:16
Go to Top of Page

akashenk
Posting Yak Master

111 Posts

Posted - 07/24/2006 :  20:17:19  Show Profile
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

United Kingdom
22415 Posts

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

Kristen
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 07/24/2006 :  23:27:35  Show Profile  Send jen a Yahoo! Message
it's under the
tables>>tablename>>triggers

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

akashenk
Posting Yak Master

111 Posts

Posted - 07/25/2006 :  09:29:38  Show Profile
Ahhh.. that makes sense. Thanks for all your help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000