Thursday, March 26, 2015

Simple SQL Audit Trail

Snow Covered WV State Capitol
"We have a problem!", my boss exclaimed.

Our online system was showing a charge to an account that was different than what it was supposed to show.

I pulled up the relevant section of code.  There was no logical way this code could have provided that value.

"Did you change the record manually?"  I asked.   Every so often, he has to manually intervene to fix an entry error.   I was guessing this was one of those times.

But he didn't remember, and there was no audit records on the database.  Luckily, I was able to confirm with another coworker that he had modified the record, so I was spared hours of combing through code, looking for an error that didn't exist.

Since the next time, I might not be so lucky, I decided it was time to start auditing, using a trigger.

For those who don't know, a database trigger is a subroutine that the database runs on an event, like inserting or updating a record.  It doesn't matter how the record changed, the system will execute the trigger.

I found the perfect solution here.  This trigger will record all inserts, updates and deletes with user name, date and fields.

The trigger pulls the SYSTEM_USER property to get the user that made the change, and does some really nice work to pull the field names, old and new values together.  It is excellent!

https://www.simple-talk.com/content/article.aspx?article=301