SQL Audit Part 2: Overall Description of the Flow of Data

Now that we have set the ground rules for our little discussion, let me take some time to explain how I wanted the data to flow.

The image above shows you what I aim to do.  On inserting, deleting or updating data in the Source Table, I want to store that change in the Source Database (the Audit Table in the Source Database above).  Then, for "old" audit entries, I want to move them off the source database and onto an archive database (in this case called the Audit Controller Database) so that they don't take up space in the source database and could in theory be saved to disk to be kept for prosperity.  "Old" can be a variable time, in my mind calculated in days, some source tables would have a small "old" value, whilst others would have a large one, all in the same application.

All of this would be managed by triggers (to audit the data and store it in the audit table) and stored procedures to create the audit table and archive tables, to create the triggers, to maintain the triggers and tables, and to archive the audit table data to the controllers database.