Unanswered: Stored Procedure to Delete from multiple tables?
Language: ASP 3.0 with VB Script
Platform: Windows 2000
SQL Vers: MS SQL 2000
Just when I'm beginning to build up a little confidence with MS SQL, I hit another brick wall. I have written an ASP Application for a tutoring service that tracks personal information for Customers and Tutors. So, I created a field in all the various tables to uniquely identify each user: CustomerTutorID.
The database is slowly getting bloated, however, so I need to delete customers or tutors when they stop using the service. I need to be able to pick a single CustomerTutorID and delete EVERY Record that has is associated with that unique ID. Now, this ID is NOT a primary or foreign key, and I can't make it one (the reasons why are complicated, but valid: the ID is sometimes brought in from QuickBooks or generated as a random number by the ASP app).
I think I need a Stored Procedure to do this, but I'm not sure. I've read things about Cascade Deletes and am confused about how to go about this.
I would like the admin to be able to:
1) Select the Customer or Tutor name from a dropdown menu (this will provide us with the CustomerTutorID)
2) Click a submit button on the bottom of the form
3) Have the system search ALL tables in the database and delete EVERY record where the CustomerTutorID equals the number provided.
4) Return a "Operation Successful" response (or redirect us to a page with that message.
Am I on the right track with trying to do this as a stored procedure? Is it possible to search through all the tables, or do I need to specify each table that it looks in? This might be a problem because some of the tables (such as CreditMemos) may have info for one client, but not another.
I like vextout's creative approach, but I can't recommend this method for an application. It is too easy for future developers/debuggers to create objects that reference the same IDs by a slightly different name, (CustomerTutorManagerID, CustomerTutorIDOld, CustTutID, etc...) or to create tables where it is undesirable to delete records that reference contain a CustomerTutorID column (History tables, for example).
It is good programming practice to explicitly delete from each appropriate table.
I see your point (no pun intended). It's unrealistic to expect the Admin to go through and delete each reference from about 12 tables (to be blunt, she's barely smart enough to log on and handle the bookkeeping tasks).