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-21 : 12:03:35
|
| Hello,I have 3 tables, which are related: Users [UserId]PK, [UserName]UserDocs [DocId]PK, [UserId]FK, [DocUrl]DocComments [CommentId]PK, [DocId]FK, [Comment]I created 3 procedures:1. DeleteUser Delete a user for a given UserId.2. DeleteAllDocs Delete all docs for a given UserId3. DeleteAllComments Delete all comments for a given DocIdIn DeleteUser I EXEC the procedure DeleteAllDocs with UserId so I can delete all user documents.But how can I call DeleteAllComments in DeleteAllDocs if I have only the UserId?Thanks,Miguel |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 12:10:22
|
| You will have to modify DeleteAllDocs in the following manner:1. Find all docID for given userid which need to be deleted2. Delete all comments for each DocID3. Finally, delete all docs for given useridHarsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Jaap
Starting Member
19 Posts |
Posted - 2007-03-21 : 12:16:11
|
| Why do you use to delete the records by procedures?When you add On CASCADE DELETE on the tables the records will be deleted automaticly.It is possible to delete them by trigers on the tables.Open in a trigger ON DELETE a cursor on SELECT UsedID From deleted and loop the cursor. In the loop you can EXEC the procedure.Also a trigger on the table UserDocs and EXEC in the loop the procedure DeleteAllCommands.Jaap |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-03-21 : 12:41:15
|
| Jaap, Where do I set Cascade Delete of a table?How can I do this?Thanks,Miguel |
|
|
|
Jaap
Starting Member
19 Posts |
Posted - 2007-03-22 : 04:10:45
|
| Make a diagram and put in your tablesCreate relations on the tables and in the properties you can create ON CASCADE DELETEJaap |
|
|
|
|
|
|
Delete "Child" records