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 |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2007-03-19 : 19:39:01
|
| Hello,I have 2 tables: Users and Documents.Then I have many other tables which are related with FKs to Users and Documents.I want to delete all the dependent records on all tables when I delete a document in Documents table. However, I don't want that to happen when I delete a user in Users table.How can I do this?Thanks,Miguel |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-19 : 19:50:41
|
| 1. Use cascading deletes for your foreign key constraints. 2. Add the logic to a delete trigger.3. Add the logic into the stored procedure.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-03-19 : 21:27:57
|
| I suppose those are the 3 options.I though that using cascading deletes was the way to go.To add the logic to the stored procedure, is this usually done?And can I run one procedure from another procedure?Thanks,Miguel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-20 : 12:50:18
|
| We use cascading deletes in one application and in another we put the logic in a stored procedure. Cascading constraints become a problem when you run into circular references, so you end up having to put some logic elsewhere such as in the stored procedure. And yes you can call sprocs from other sprocs.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Delete Records