Append Only

I’m familiar with the archive engine in MySQL, including its limitation of “no keys allowed”, but how do you achieve a similar setup with MSSQL? Our preference would be to use the append-only feature of the archive engine with auditing to create a table that is more resistant to tampering for obvious reasons, but on the MSSQL platform. Any ideas?

There isn’t exactly an out-of-the-box set of features that I’m aware of, but I’ve never really tried to set this up, so someone else might have a better suggestion. However, the first ideas that pop to mind for me:

  1. Lock the table permissions down to only allow select for most users… and then perhaps create a special user for Ignition that is used for inserts.
  2. Use triggers to audit changes (a quick search proved this seems to be a common enough approach. See here for one example)

However… I’m afraid the trigger-audit method might be a little inefficient. With the importance of data integrity, especially for situations like those dictated by 21CFR11, I feel like I must be missing something that is likely new for SS 2005 or 2008. Hopefully others will post as well!

Good luck,