January 8, 2011 at 11:39 am
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
Why don't you use a foreign key concept and the cascade delete option?
January 9, 2011 at 10:20 am
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
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