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.
- 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.