Post reply

delete cascade

  • September 9, 2009 at 5:14 am

    #209757

    Hi, i'm new in this and i've been searching for a long time on google to find some good exemple.

    I need a stored procedure that can delete a record from parent table, but first to check if there are some records in child tables and if there're then delete first all records from child then delete record from parent table.

    Tnx for the help!

  • September 9, 2009 at 5:17 am

    #1050983

    You should be using DRI ( Declarative referential integrity ) to do this.

    Try this link http://www.nerdymusings.com/LPMArticle.asp?ID=34


    Clear Sky SQL
    My Blog[/url]

  • September 9, 2009 at 5:23 am

    #1050987

    Any exemple how to do this?

  • September 9, 2009 at 5:27 am

    #1050989

    In the link above under "Cascading Actions"


    Clear Sky SQL
    My Blog[/url]

  • September 12, 2009 at 12:36 pm

    #1052728

    Hi,

    there is no need for stored procedure..

    You can do it by making foreign key with delete cascade

  • September 12, 2009 at 1:45 pm

    #1052734

    I don't like cascade delete foreign keys because they can affect hidden huge data operations. My suggestion is an own, custom delete procedure.

  • September 13, 2009 at 12:02 am

    #1052775

    Both approaches (cascading deletes and custom procedures) have strengths and weaknesses.

    In a well-designed database, cascading triggers are often to be preferred since the server implements all the necessary checks and controls to ensure that the data remains consistent. It is all too easy to make subtle errors in custom procedures - most frequently concerning concurrency. Even quite experienced developers and DBAs struggle with this.

    In addition to Mr Ballantyne's link, make sure you read The Official Documentation thoroughly.

    If you tell us more about your requirements, we may be able to make further recommendations.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 7 posts - 1 through 6 (of 6 total)

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