Post reply

delete stored procedure for relationship many to many

  • January 8, 2011 at 11:39 am

    #388586

    Hi,

    I have the tables :

    - Customer (CustomerId, Name, ..)

    - CustomerAddress (CustomerId, AddressId, TypeAddress)

    - Address (AddressId, AddressLine1, AddressLine2, ZipCode, City, ..)

    TypeAddress refers to a TypeAddress table.

    One customer can have several addresses which differ by TypeAddress.

    One address is only for one customer.

    I use a relationship many to many because I have also the tables (with the same Address table) :

    - Supplier (Supplier Id, Name, ..)

    - Supplier Address (Supplier Id, AddressId, TypeAddress)

    - Address (AddressId, AddressLine1, AddressLine2, ZipCode, City, ..)

    When I delete a customer, I need to delete all Addresses of this customer.

    It is to said : delete in tables Address, CustomerAddress and Customer.

    I prefer to do that in a stored procedure than with triggers.

    Have you some codes or links to solve that ?

    Thanks for your help.

    Dominique

  • January 8, 2011 at 1:03 pm

    #1271081

    Why don't you use a foreign key concept and the cascade delete option?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 9, 2011 at 10:20 am

    #1271162

    Yes and No.

    If I put On Delete Cascade on the two FK, when I delete a customer then the CustomerAddress delete also (it is for the Yes).

    But never the Adress will delete (it is for the No).

    Some research on Google, said to use trigger to do that.

  • January 10, 2011 at 6:50 am

    #1271357

    If you don't want to use a trigger, use multiple DELETE statements within your SP. Delete the proper "related" records (based on key value) before deleting the main record(s).

Viewing 4 posts - 1 through 3 (of 3 total)

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