Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2003
    Posts
    21

    Unanswered: Cascading Deletes

    When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.

    Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?

    thanks
    dog

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    No, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What!?!?!?

    SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.

    Triggers are NOT necessary for standard cascading.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cascade? Like a waterfall?

    Has anyone scanned the landscape for a merry-go-round?



    Wholly disconnected ramblings bart man....

    Seriously...be careful with cascading...should be no need...

    I never liked messing with keys...

    damn surrogates....

    To me, if a key changes, then it's a new entity...or the key is defined improperly...

    You lose all history....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.

    Disconneted ramblings....

    ...many...non-sequiturs....

    Wish I had a key for elipsis so I didn't have to hit the period key three times...

    Must....complete....sentence..... damn!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2004
    Posts
    28
    Quote Originally Posted by Brett Kaiser
    Seriously...be careful with cascading...should be no need...
    pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Wow...dogmatic....

    Cool....

    You want to cascade....knock yourself out....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2004
    Posts
    28
    Quote Originally Posted by blindman
    SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
    That seems to be the logical place for it, however the only options I have are:
    Check existing data on creation
    Enable relationship for INSERT and UPDATE
    Enable relationship for replication

    maybe a version difference

  9. #9
    Join Date
    May 2004
    Posts
    28
    Quote Originally Posted by Brett Kaiser
    You want to cascade....knock yourself out....
    That's your solution?

    WOW.......
    COOL DUDE.........
    THANKS FOR THE MOST RIGHTOUS EXPLAINATION...........................
    ATS AWESOME.....................

  10. #10
    Join Date
    May 2004
    Posts
    28
    Quote Originally Posted by lcsgeek
    Enable relationship for INSERT and UPDATE
    Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.

    Again, do I need to write triggers to accomplish my goal here

    I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See attached screenshot.
    Attached Thumbnails Attached Thumbnails CascadeDelete.bmp  
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2004
    Posts
    28
    Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.
    Attached Thumbnails Attached Thumbnails rel.jpg  
    Last edited by lcsgeek; 05-24-04 at 15:40.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    'bout time to upgrade, isn't it?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Whodah Thunk It?

    cascade update\delete is "New" to sql 2000 v

    and you dont have to create database devices anymore..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •