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.
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 |
|
|
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? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-24 : 12:40:06
|
Somthing like this:CREATE TRIGGER dbo.MyTriggerON dbo.MyEmployeeTableAFTER DELETEASSET NOCOUNT ONDECLARE @MyTempTable TABLE( T_Employee_ID int NOT NULL, PRIMARY KEY ( Employee_ID ))-- Prepopulate with Employee numbers of all record(s) being deletedINSERT INTO @MyTempTable(T_Employee_ID)SELECT D.Employee_IDFROM deleted as D LEFT OUTER JOIN inserted AS I ON I.Employee_ID = D.Employee_IDWHERE I.Employee_IDDECLARE @intRowCount intSELECT @intRowCount = 1 -- Force first iterationWHILE @intRowCount > 0BEGIN -- 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 = @@ROWCOUNTEND-- Delete all emloyees located as decendents of supervisorDELETE EFROM dbo.MyEmployeeTable AS E JOIN @MyTableTable ON T_Employee_ID = E.Employee_IDGO Kristen |
|
|
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? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-24 : 23:08:28
|
Sorry, not used SQL 2005 or SSMSKristen |
|
|
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... |
|
|
akashenk
Posting Yak Master
111 Posts |
Posted - 2006-07-25 : 09:29:38
|
Ahhh.. that makes sense. Thanks for all your help! |
|
|
|
|
|
|
|