Triggers for Auditing
In response to our last two editorials on the love/hate relationship with triggers Marc writes about how he likes to use triggers for auditing data modifications. Marc Says:
Whilst I know that there always exists many ways to accomplish any given task, there is one area where I find triggers the most useful approach. This is in handling data audit / data modification security functionality.
A common application requirement is to log user changes to data. Of course this could be placed in the business logic layer, but unless additional functionality is required, simple triggers on the tables that need to be monitored for user changes can log the changes in a data audit table (time stamps, user info etc.). One benefit of this approach is that this can either be added during database creation or even after an entire application is created, and is not affected by bulk set updates.
Maybe I’m missing something, and there is a preferred approach, but for what it’s worth, I have found this very effective in previous projects.
I agree with Marc regarding the ability to add auditing even after a database has been placed into production by the use of triggers.
What happens if/when you find you need to use another data engine? How do you introduce auditing when you no longer have triggers available to you?
To be fair, changing data engines seems to be a fairly rare occurrence. If using some other technique to track data modifications requires a lot more time enabling you to easily change your data engine, and you never do change your data engine, was it a justifiable expense?
Since the future is hard to predict, unless I had a non-trigger method to do auditing that could be implemented as easily as generating triggers, I would be unwise to at least consider using triggers for auditing.
Thanks Marc for keeping us thinking. Perhaps you may have questions that should have been asked when deciding to use triggers? Share your thoughts by email to btaylor@sswug.org, or leave a comment below to get into the conversation.
Cheers,
Ben