One method I've used is to create a history table for each of the the tables in database. These tables are a mirror of their original tables, except without a primary key and the addition of a date field (though this may not be needed if you are already tracking the last update time). All my tables have a Last Update User, and Last Update Time field that are either updated by my stored procedures (i.e. pass in the Session.username and store this value in the last update user, and the current timestamp in the last update time).
Then, with a trigger, you can say that whenever a record is updated or deleted, copy the current data into the history table, BEFORE applying the new changes. This will store the old record, and the current record is always in your current table. Then, if you need to, you can grab all the pertinent records from the current table and the history table, and see a historical record of the changes made. Using the triggers is a bit of a pain initially, but once they're set, you don't have to worry about auditing anymore (at least not until you change your table structure) - it's done automagically. IF you want to implement reporting based on the history records, then just be aware that you will run into some ugly SQL trying to make this work - it does work, but can get messy. Hope this points you in the right direction. Shawn On Monday 14 February 2005 18:47, Jeff Chastain wrote: > I am trying to implement history tracking functionality for a system. For > example, I need to know that John Doe changed object x on date y. Even > better would be being able to take that a step further and see that John > Doe changed the data in table a, column b from 'abc' to 'xyz' and > therefore, I could undo this transaction. To make this one step more > complicated, the objects that I am tracking history for are organized in a > hierarchical fashion and I would like to be able to see the history for one > object including any changes made to any child objects. > > I want to implement this as generically as possible so that as this system > grows, the history tracking won't break. Even better, I might be able to > reuse it in another app. > > So, does anybody know of any examples or resources that I could look at for > some ideas? I am not even sure what to Google for this one. > > Thanks > -- Jeff > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194648 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
