Rule of thumb for me is that every DELETE or UPDATE query should start life as a SELECT query.
The eight stages of DELETE:
- SELECT what you're deleting;
- fix the mistake in the query (there's always one);
- SELECT again;
- BEGIN TRANSACTION;
- SELECT to confirm the right deletion -- twice for good measure, and maybe ROLLBACK once or twice if you're feeling twitchy;
- Hover finger anxiously over the un-entered COMMIT command for several seconds while resisting Dunning–Kruger effect;
- COMMIT. :)
- Give the table an DeletedTimestamp and DeleatedReason column.
- Mark the records as deleted.
- Run around like a lunatic and fix queries so they don't show "deleted" columns to the rest of the app. Rather create a "view" which never sends out "deleted" columns and rows.
- Make a page in the application where people can "restore" deleted items. Because they do stupid things too once in a while