|Snow Covered WV State Capitol|
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!