I think the easiest way to do it would be using triggers on the table itself for update and delete. We already do have a createID, createDate, updateID, updateDate on the tables, so we're already to go on that. That'll only save the last update thought, and we want to have the info saved.
As for deleted records, we'd leave the history table alone when that happens to preserve the data in case we need to go back and look at it. When a user is removed from a system, it's just a case of unchecking an active box in the app, which updates the row, setting them inactive, and recording an endDate to their access so we can track when they were disabled.
As for speed, the system isn't high impact, nor large volume, so I think we're cool.
I guess my only question is to how we get the information of the user who is disabling the person, but since they're logged into the system, it should be pretty simple. I'm shaky on front end, so I'll talk to my developer about passing that to me so I can use it in the table update.
Sean Lange (5/21/2013)
Unless you have a mountain of tables and/or expect lots of changes this is about the simplest way to do to it. I would actually have a whoChanged and whenChanged on the main table. Then you just audit that right along with the rest of it.
Now comes the question of what causes the audit record to be inserted? Do you do this with a trigger or inside the CRUD proc?
You might even decide that audit rows are kept or deleted when the row in the main table is deleted. If you keep the history you can still have history for rows that are no longer in the main table.
For the trigger approach you would need to add both an update and a delete trigger. You can make use of the UPDATE function here too so you only track this if certain columns change (if you want). For the sproc approach you would need to have auditing logic in both sprocs.
Just keep in mind that this has the potential to slow down the system if you are not careful in how you implement this.