I though this is an audit trail system, but why the design look like a database log? I have this feeling at the design stage. If I make a database log, I understand that the IO transaction will gonna eat up the resources, but we need to judge what is the most important one? Resource cost how much? Data integrity cost how much. It has been a hard time for me to put this consideration when designing the system. Thus I decide to make both.
There are few pieces in this design.
- Parameter table - Allow owner to decide which table that gonna need to audit. Just register here and let the audit engine do the rest.
- Stored Procedure - The audit trail engine. It read information from parameter table on what is needed to audit, create trigger and tie up with the original table, spawn a new shadow table from original table.
- Trigger - Act as database logger.
- Shadow table - Contain pre and post data of original table.
- Audit trail table - The main table that tells the summary story of shadow table.
The whole thing was made up from 10% work on JAVA, 60% work on Dynamic SQL and 20% work on Stored Procedure. I try not to limit the design only for JAVA, thus a lot of consideration need to think of when designing this shit.