Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting Grandfather, father, child records Expand / Collapse
Author
Message
Posted Tuesday, May 18, 2004 9:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:19 AM
Points: 222, Visits: 179

Hi All.

Can someone help with the following please.

3 tables BudgetCategory, Budget and BudgetHolder in a Grandfather, Father child hierarchy

BudgetCategory - CategoryID, CategoryDesc

Budget - CategoryID, BudgetID, BudgetDesc

BudgetHolder - BudgetID, StaffName

If I delete a BudgetCategory record, I need to also delete all related Budget records and all BudgetHolder record related to each deleted Budget record.

How do I do this in T-SQL. Should I be using a trigger. If so what should I code.

Thanks for any help.

CCB

 

 




Post #116444
Posted Tuesday, May 18, 2004 12:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 1:07 PM
Points: 222, Visits: 22
There are several ways to accomplish what you are trying to do.  Triggers are one option.  Another option is to set up the tables as described in BOL.  Do a search on "Cascading Referential Integrity Constraints".  The quick version is that when you create the father and child tables you can set up the foreign key reference constraints to cascade when a row is deleted in the parent table.  I have heard debates pro and con whether this is a good strategy to use.


Post #116482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse