In one of our applications, there is a requirement that data not be permanently deleted (or at least not initially).

The original way I implemented this was to add a DELETED bit column to each table, and if that column was set to 1 (true), then I would treat that row as if it didn't exist.

This is achieved through ensuring access is via a View that excluded those rows, stored procedures, and also adding an INSTEAD OF DELETE trigger that intercepted regular DELETEs and converted them into UPDATEs to flip the column bit.

That works pretty well, but I'm trying to weigh it up against the other approach I can think of, which is to have a separate table that only holds the deleted rows.

eg. if the original table was FRED, then the other table could be named DELETED_FRED (just to be clear what it was for!).

I'd guess you could still use a trigger to catch any deletes to table FRED, and use them to copy the deleted row over to DELETED_FRED.

I suspect that the latter approach probably makes it easier to use some kind of ORM framework, as you don't have to be careful about avoiding the deleted rows.

The advantage of the former though is that you maintain your referential integrity for all of your data. Because of the relationships of other tables besides FRED, I don't think I could do this with the DELETED_* tables.

I'm curious as to what other pros and cons are of these approaches, and whether there is a formal name for this kind of thing?

There are some resources I have found: