How to Delete Parent Record if Child Record is Deleted - MS SQL Server

I have two tables:

Customer
Phone

Customer has PhoneID as foreign key which points to PhoneID pk in Phone table.

If a customer record is deleted, i want the associate phone record in phone table to be deleted also. Obviously, i can not use ON DELETE CASCADE because in my case, i'm cascading deletion in reverse manner.

Any idea what to do?
LVL 1
F-J-KAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
halfbloodprinceConnect With a Mentor Commented:
Try this out:
In your Delete SP:
Fist select the phoneID
Declare @PhoneID Int
Set @PhoneID = (Select PhoneID From Customer)
Where CustomerID = XXXX (assuming this is the record you want to delete)
Now
Delete From Phone
Where PhoneID = @PhoneID

And then delete the customer record
Delete from Customer
Where CustomerID = XXXX
0
 
F-J-KAuthor Commented:
Thanks. I will give it a try, by the way, i'm trying to set it systematic/automatic.
0
 
F-J-KAuthor Commented:
In short, i want to delete a parent record if a child record is deleted. ON DELETE CASCADE only works for the opposite way.
0
 
VishnukumarConnect With a Mentor Commented:
You can make use  TRIGGERS to invoke the event to delete the record of the parent table. This will function automatically
http://www.sqlteam.com/article/an-introduction-to-triggers-part-ii 
 
0
 
F-J-KAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.