Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: