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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-24 : 07:46:20
|
| Brian writes "Greetings Guru!I see that you have a few posts on your site on using a trigger to delete data from another table using a cascading delete trigger. But will this work if you have multiple tables that require items deleted?Here is a little example.3 Tables DISTRICTS (id_district NUMBER)USERS (id_user, NUMBER, id_district NUMBER)SERVICE_REQUESTS (id_service_req NUMBER, id_district NUMBER, id_user NUMBER)How would we write the trigger for when a DISTRICT was deleted to delete all USERS and SERVICE_REQUESTS of that district? If you had not guessed I run TigerTracking.com and try to offer a free inventory and service control center for K-12 schools. If you can help, it would help everyone!THANKS!Brian" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-24 : 07:50:20
|
| First of all, if you're using SQL Server 2000 then you can create cascading foreign keys to perform the deletes. If you're NOT using SQL2K, then the following code should help:http://www.sqlteam.com/item.asp?ItemID=8595It's not a trigger, but it will navigate through the related tables automatically and delete the relevant rows. And if you needed a trigger, the following should work:CREATE TRIGGER CascadeDeleteDistricts ON Districts FOR DELETE ASSET NOCOUNT ONCREATE TABLE #users(id_user NOT NULL)INSERT #users(id_user)SELECT u.id_user FROM Users U INNER JOIN deleted D ON U.id_district=D.id_districtDELETE SFROM Service_Requests S INNER JOIN #users U ON S.id_user=U.id_userDELETE UFROM Users U INNER JOIN deleted D ON U.id_district=D.id_districtDROP TABLE #users |
 |
|
|
|
|
|
SQL DELETE TRIGGER