My question pertains to anyone with experience with CFR 21, Part 11. This deals with audit trails, their scope and how to make them effective and efficient.
I am currently adding audit trail capability to an application. Past experience, and current, tell me that an audit trail requires a hell of a lot of memory and a hell of a lot of processing. I have created a audit trail table (no relationships set) that does nothing but record who in the database, what they are doing in the database (changes made to records) and when they are there.
Scope and memory is the first issue. How much detail and what should be included in the audit trail (This is where you need to know CFR 21, Part 11)? For instance, one of the applications is for document handling (kind of like Documentum, but much better than that POS). A document going through the change process would generally have 4 tables that contain what would be considered critical data. In the data transactions from creating a change request record and document record to the point where the records are complete there could be well over a thousand transactions that would need to be recorded to the audit trail. Each one of those transactions equates to an additional record to the audit trail table. Of course the table would have to be frequently archived, which will relieve some of the memory strain. But I am wondering what anyone's thoughts about the scope and detail that should be included in these records (if you have any) such that memory usage could be minimized?
The second issue is processing. As noted above, there are a hell of a lot of transactions occurring with the audit trail table and it would be my guess that a lot of processing time will be/is used for this activity. My current approach is that I have a function that opens the table, creates a new record then drops in whatever data is required to fill out the record then saves the record. The function is invoked at any point in the application that I determine that it is required, like anytime anyone logs in. Is this the only approach available or is there a better way to look at how to more effectively maintain an audit trail?