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.
Best Answer
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. :)