Deleting Grandfather, father, child records

  • 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

     

     

  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply