How to delete from a database?

Question

I know of two ways to delete data from a database table

• DELETE it forever
• Use a flag like isActive/isDeleted

Now the problem with isActive is that I have to track everywhere in my SQL queries that whether the record is active or not. Using DELETE however gets rid of the data forever.

What would be the best way to backup this data?

Assuming I have multiple tables in a database, should I have a common function which just backs everything up and stores it in another table (in XML probably?) or is there any other way.

I am using MySQL but am curious about techniques used in other DBs as well.

Answer 1

Replace the table with a view that hides the inactive items.

Or write a trigger on DELETE that backs up the row to an archive table.

Answer 2

You could use a trigger that fires on deleting records to back them up into some kind of graveyard table.