Need to create a generic audit trail for sql tables in a Visual Studio 2005 / msSQL 2005 application that can be called via a table trigger.
We need to have a generic trigger that we can attach to any table in the application as the need arises. We don’t anticipate needing to audit trail all tables, but we want to be able to add and remove the trigger with nominal fuss.
The audit trail needs to create one record per change.
If a new record is being added, one record to note the creation date.
If a record is being deleted, one record to note the deletion date and store current data of the record in Value_Before as XML.
If a record is being changed, one entry in the log file per field that has been changed.
Logfile layout is attached. I’m open to any changes to improve the functionality.
One of the logfile fields is the user that made the changes, at this point I’m assuming we’ll have to modify any table and add a field with the user ID as we have not been able to figure out how to pass session variables to the trigger. If you have a clever idea, I’m open to anything.
Deliverables need to be the trigger working on a sample database and instructions on how to implement the trigger on other tables. Adding above mentioned fields or any changes that need to happen.
Feel free to ask any questions as this description seems awfully brief, but I didn’t want to babble on unnecessarily, either.
——————————————
Answers to questions that have been asked:
——————————————
I see no need for blob storage. All of the data is name / demographic / transactional in nature. On average, large fields will float at 50 characters. There may be an occasion couple of thousand character note, but no image tracking or other large chunks of data.
