Post reply

possible to do a cascading delete

  • December 20, 2003 at 6:32 am

    #81806

    Hi,

    (probably a stupid question, but anyway)

    Is there any way to do a cascading DELETE, even if I don’t have cascading DELETE set on the tables in question?

    What I mean is, say I have a product, and I’m not sure how many tables etc that this product is a foreign key, Is there any way to have a query that will:

    1)DELETE all the instances of this productId.

    2) DELETE the product in the table where it is a primary key?

    Could I do this by simply passing the “productId” to the sproc, or, would I have to specify the names of all the tables in which this product is a foreign key?

    Many thanks,

    Yogi.

  • December 20, 2003 at 3:06 pm

    #487352

    Don't you trust SQL Server to handle this?

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 21, 2003 at 4:48 am

    #487356

    sorry man,

    it was a daft question.

    cheers,

    yogi

  • December 21, 2003 at 1:18 pm

    #487368

    There are no stupid questions!

    As one table can be potentially linked with each other in a database, how should this work that you only supply an identifier without stating what to do with it?

    So, I guess it's up to you to take up the parameter and process it.

    But SQL Server offers such a functionality, so why reinvent the wheel?

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 21, 2003 at 2:40 pm

    #487370

    I agree.

    I think I'll stick to learning how to work the wheel before I try to reinvent it 🙂

    cheers,

    yogi.

  • December 21, 2003 at 5:02 pm

    #487371

    If you're using SQL Server 7, then you don't have built-in cascading deletes. In that case you have a couple of options.

    1) You build stored procedures to handle the deletes. Reason being if you try to use triggers, they don't work if foreign key constraints are present. The attempt to delete at the topmost table will result in a key constraint violation and the trigger will never fire. Therefore, the stored procedure will have to start from the bottom table and delete on the way up to ensure the key contrast violation never occurs.

    2) You don't use foreign key constraints and you implement triggers (since there will never be a violation, the triggers will fire.

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    Edited by - bkelley on 12/21/2003 5:02:25 PM

    K. Brian Kelley
    @kbriankelley

  • December 21, 2003 at 8:38 pm

    #487379

    Have a look at http://www.sqlservercentral.com/scripts/contributions/445.asp.

    Need to add the fact that your are interested in ProductID only, not all foreign keys.

  • December 22, 2003 at 12:25 am

    #487381

    Whichever way you're going to do this delete-cycle. Remember to provide the propre indexes (FK) to speed it up ! Keep their columns in exact column-order(as defined on the parent-table).

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • December 22, 2003 at 5:36 am

    #487399

    Thanks folks, for taking the time.

    Great script.

    cheers,

    yogi

Viewing 9 posts - 1 through 8 (of 8 total)

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