Yes, I am working on Thanksgiving. Lets see who else.Question: Is there a solution to perform deletes on multiple tables based on the same complex rules without using Cursors or Temporary Tables?Details: This is a problem primarily about elegance in design and secondarily about performance: How to make something faster that currently takes virtually no time to process?Assume a table of some entity and another table that references it:Entity Table (entityID [Primary Key], entityData)Group Table (groupID, FK_entityID, groupData)FK_entityID references entityIDThe foreign key relationship is enforced as a constraint and updates to entityID are cascaded (propagated / escalated). Deletes, however, are not cascaded, because some (not so smart) user/developer/dba might delete entities and the associated groups, which others might expect never to be deleted. So the entities currently cannot be deleted unless all referencing groups are removed 'manually' beforehand.The problem now is that the application (which is smarter then most people) NEEDS to delete entities. Assume we want to delete based on some rules:SELECT entityIDWHERE (some rules to select entities to be deleted)
TWO(!!) delete statements would have to be written, one for the group table (first) and then for the entity table.
DELETE groupWHERE FK_entityID ...DELETE entityWHERE entityID ...
Am I correct here? There are no multiple table deletes in a single delete statement, right? So it means, I either loop through the list of entities to be deleted using a CURSOR (argh! – but current solution) or put the list of entities in a temp table (also argh!) and use the temp table either as derived table or sub-query in both delete statements. Theoretically, I could run the select statement to get the list of entities to be deleted for each delete statement, but the rules can be quite complex (involving sub-queries) and I would have to change it in two places every time the rules change.
So, is there a solution to perform deletes on multiple tables based on the same complex rules without using Cursors or Temporary Tables?The reason that I care about this is that this 'maintenance' procedure is called very frequently to 'maintain' some level of integrity in an environment that, as a whole, lacks consistency. I want the fastest solution possible, so I can call this 10 times a second or more often without a worry. The number of entityIDs matching the delete rules is usually very small (less than 10). Any solution should avoid triggers and similar features.Thanks for the help in advance.
Multiple DELETEs *NOT* using Cursors / Temp Tables
Posted - 2002-11-28 : 14:50:02
If you have SQL 2000 you can use "cascading deletes". These will delete rows in foreign key tables automatically. Look up Books Online for the details.However, If you can bring yourself to write two queries, I would do it that way. This is your business data we are talking about, I personally prefer things only get deleted when I explicitly delete them.Damian 