Re: SQL Delete from multiple files

On 21 May 2013 12:08, Luis Rodriguez wrote:

I agree that using referential integrity would simplify the issue a
lot but, if not available, a work-around would be using a trigger.
Like a RI constraint it would be valid no matter which interface is
used (DFU, RLA program, etc.).

The DELETE activity implied in the Opening Post (OP) did not seem conspicuously to be easily resolved by RI; least not from what I inferred.

The second DELETE could effect the deletion of way more than just child rows, as the effect of a CASCADE. That second DELETE was also not really compatible with a standard row-level DELETE trigger on the first file, to initiate the deletion from the other file; i.e. for the same reason, that the rows to be deleted from the second file are not conspicuously related to the first. For each deleted row, had the second DELETE been effected, the rows that were to be deleted using the first DELETE would no longer meet the selection requirements; i.e. such a TRIGGER could actually defeat the intention of the first DELETE, and thus actually prevent the first DELETE From deleting the intended rows.

However a statement-level trigger on the first TABLE could possibly be able to effect the second DELETE, but applying that second delete as an effective /business rule/ to every DELETE on the first TABLE seems a bit sketchy; unlikely an intended or desirable effect.? Would the trigger have to inquire of the ever-so-daft /who called me/ to decide whether or not to perform the second DELETE?

The Instead Of Trigger (IOT) however could be implemented on a VIEW that defines exactly what data should be deleted and from which TABLEs, and what determines if the logic is performed is the VIEW against which the DELETE is performed, not some illogical /who called me/ inquiry trying to decide. I just posted a reply with an IOT example [that I could not test].