Problem deleting records on table with trigger to delete other records

Problem deleting records on table with trigger to delete other records

I'm not a DBA, just an ASP developer. I have a table, aUser, with a trigger called DelUser that forces a delete from several other tables when deleting a record from aUser. I need to delete record from aUser where the userid is NOT in another table ("indExport"). The table "Webuser" is one of the tables in the trigger. My SQL is: 
 select count(userid) from Webuser where not exists (select * from aUser where Webuser.userid = aUser.userid AND aUser.remoteid = indExport.inum).
The query goes on forever, and after 30 minutes, I cancelled it. Question: should I delete records in the trigger-referenced tables before I delete them from aUser? I'm at a total loss here.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

In general, yes, that is the best practice. As for the query you are running, you said it goes on forever. What part of it goes on forever? Why does it go on forever? How are the tables indexed? And to top that off, you have created a query with a cross product in it which is going to kill your SQL Server. You are creating a three way cross product to be exact between aUser, Webuser, and indExport.

 

For More Information

This was first published in June 2004

Back to top