Track deleted rows/records

Question

A project has the requirement that it shall not be possible to lose data from certain tables. That is, if I "delete" a row, that row's information should somehow be preserved.

Additional information

• It is fairly unlikely that anyone would actually ever be interested in the deleted records, so speed of access is not particularly important.
• The records being deleted may be the primary key for other records in the database; if they are the primary key, the delete will cascade to the foreign records.
• The database will primarily be accessed through an API, so I can put the logic there if that's really the best place.

On SQL Server (2005 or later), what is the best way to accomplish such a thing?

---

As a note, the internet has provided me with a multitude of solutions. One seems to stand out as the best choice, but I can see some caveats. Rather than asking whether my solution is good, it seems better to just ask for a good solution. If it would be helpful, I can edit in my ideas.

Answer 1

It sounds from your comment on Marlon's answer like these are tables whose structure changes frequently. That could make the trigger solution unwieldy.

In that case, investigate the alternative of doing a Transactional Replication publication and excluding DELETE transactions from the publication. INSERT, UPDATE, and DDL changes would be replicated to your destination table. For SQL 2005, this is the optimal solution.

If you were to upgrade to SQL 2008, you could make use of Change Data Capture. That would be the most robust solution, and easier to maintain than the Replication method. But I know getting the approval/budget/time for such upgrades is not always easy. :)

Answer 2

Create a Trigger to dump the deleted rows to another table.

I am not saying that this is the best solution but it's one of the many options.

I have a post on my blog about how to do it: http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/

Answer 3

you can also use the OUTPUT deleted.* INTO TableName clause with the DELETE statement to insert the deleted records in a target table.

Answer 4

A very easy way is to setup transactional replication and simply don't replicate DELETEs. See Set the Propagation Method for Data Changes to Transactional Articles:

To create an article that does not propagate data changes

At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, and a value of NONE for at least one of the following parameters:

@ins_cmd - controls the replication of INSERT commands.
@upd_cmd - controls the replication of UPDATE commands.
@del_cmd - controls the replication of DELETE commands.

Your replication subscriber will simply accumulate data w/o ever deleting it. If keys are reused you'll have to take care of that by adding a subscriber-only surrogate key.

Answer 5

For SQL Server 2005, you only have the option of using TRIGGER based solution for INSERT/UPDATE/DELETE operations which is nicely described here.

Note: Remember the overhead of using triggers as if you perform large deletes on table, then everything will be logged.

For 2008 and up, you can use CDC (Change Data Capture) as described here.