The simple example gives you a simple way to recover from errors when working with sensitive database tables.
Many people that work with databases, have one or many tables that hold sensitive information. Whether that's order information, billing information, even user information, it's all about how valuable it is to your organization. I too have such a table in one of my databases, and from time to time, I need to go in there and manually run some sql queries. These include delete statements every once in a while (every dba in the south just jumped), and I have been known to run it without a parameter by mistake, thus deleting more than I wanted.
Although I do back ups before running any dangerous queries like that it's just not enough because a restore can bring unwanted downtime to my application. The solution I came up with is to create another table that mimicks the structure of my sensitive table, and make a trigger for delete on the table. The trigger takes the deleted data, and writes it to the new table. What this means is that if I mistakenly delete some records, I can just copy them back ... kind of like an undo button!
Here's what the trigger looks like
CREATE TRIGGER AllocationTrashBackup ON [adMnthlyAllocation]
FOR DELETE
AS
insert into admnthlyallocationtrash
select * from deleted
Notice my clever naming of the new table ... it is after all acting much like the trash bin. Also, notice that it is selecting as the source for the insert, from the "deleted" table. This is a special table for triggers that gives you access to the row that was just deleted. The same goes for an Insert trigger ("inserted") and an Update trigger ("inserted" for the new data, "deleted" for the old). I could easily modify the trash table to hold a "datetrashed" field, and then every time this trigger is run, delete anything that was trashed more than thirty days ago so the possibilities are endless with this method.
Hope that helps someone else!