For work, we are going to have to have a complete audit trail of all our data. Here’s the strategy I’m planning on implementing.
Basically, it’s all about triggers. I’m going to implement an “AFTER INSERT” trigger, a “BEFORE UPDATE” trigger and a “BEFORE DELETE” trigger. This way, I’ll know the first time a record shows up, I’ll know what values were before they were changed and I’ll know the final state of things before any record is deleted.
I’m setting up a duplicate schema of my regular system. Every table is replicated exactly. Well, not exactly… the tables in the audit schema have no primary key and three extra columns… triggertime, triggeraction and triggeruser. The primary key is eliminated so that the same key can be inserted into the audit table multiple times. The other three fields will be used to differentiate the records. Obviously, the triggertime is the CURRENT_TIMESTAMP and the triggeraction is either “insert”, “update” or “delete” based on the trigger that’s firing. The trick is the triggeruser.
This is a ReST API that’s hosted on IIS. We have a fixed database connection string. We don’t modify it based on the user that’s making the call and we don’t have every single user with access setup as users in our database. That’d be dumb. Still, we need to know who is making the changes to our data. How?
In the stored procedures that make the calls, we are always using an access key. The access key is used to control what data the calling user gets to have access to. Using that, we can set a variable and use that as our “trigger user”.
Here’s our “saveUser” stored procedure… I know… it only inserts.
Notice how we set the @vUser variable? We can use that in our triggers. Here’s the trigger that’ll make a note of the user as it is initially created.
It happens “AFTER INSERT” which means the user record is already in the users table. All we need to do is insert that into the _audit table along with the current time, the function (INSERT) and the value of @vUser that we got from the stored procedure.
Granted, this isn’t perfect as someone can go edit the table using some other method than the stored procedure. Then we wouldn’t know who did it. It can be improved on for sure. That being said, this will do a nice job for us in being able to look at data and how it changed over time.