SQL Audit Part 7: Audit Table Maintenance Script

Now we have the audit started and working, what is needed now is to manage it.  By manage, I mean that when we change the source table structure, we don't want to have to manually alter the tables that store the audit data.

Rather than recreate slightly modified code to manage the the tables, I coded the creation script so that it will do both.  Yes, I know that this is cheating (I planned the development that way) but coding is meant to be done smartly, not just with brute force but also finesse.  So, to manage both in the one script, there are a few principals to use.

1. Check before create.

Before you do what you need to, check that it hasn't already been done.  In some cases this will mean that if it is there you do nothing, in other cases you will delete it so that you can recreate it, and in more you update instead of delete.  That is a decision that you can make based on your code, and what your wanting to achieve.

2. Understand both requirements.

Updating code and creating code can be combined, but there are some cases where this is not advisable.  You need to understand both how the creation needs to work and how the update needs to work to be able to know if the functions can be combined, and then the best way to achieve that.

3. Make your code manageable.

This is more of a generic coding principal, but it still applies.  When you combine 2 functionalities into the one piece of code, it makes it that much more complex. The best way to manage the complexity it so make sure that your code is readable, and to make sure that another can come along and understand it with without you pointing out whats what.  On this one I am continually learning, but I think I am getting better.

Then, with that in mind, I can just create a cursor to loop though the tables that have been started, and re-run the creation stored procedure over it, and were done.