Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sparrow37
Posting Yak Master
148 Posts |
 Posted - 2009-07-03 : 09:28:57
|
| Hi all,I have created a database and related stored procedures to insert update and delete data. I want to make sure that if a parent record is deleted then its childs are also deleted. How can i od this in stored procedure. How can I determine that I need to delete child records of this parent ?Regards,Asif Hameed |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-03 : 09:51:23
|
If relation between tables is properly set, you cannot delete parent by mistake because the system will give an error.You can use cascading delete to make sure childs are deleted when parent will be deleted but that is not fine.You should code your SP by knowing the relationship and doing the deletes in the right order.Or what is your question?GreetingsFred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-05 : 03:00:37
|
quote:
Originally posted by sparrow37
Hi all,I have created a database and related stored procedures to insert update and delete data. I want to make sure that if a parent record is deleted then its childs are also deleted. How can i od this in stored procedure. How can I determine that I need to delete child records of this parent ?Regards,Asif Hameed
i think easiest way to do this is to set ON DELETE CASCADE property while defining foreign key property b/w parent and child table. This will ensure dependent records are automatically deleted when parent record is deletedAnd if you really want to do this in procedure, you need to use new OUTPUT operator to get details of deleted parent records in a temporary table variable and then use it to delete records from children tables |
|
|
|
|
|
|
parent child relationship