>We want to start auditing the database changes made in our home grown >CMS app. Anyone on here done this or have suggestions on where to >start with some thing like this? > >-- >Phil
Hi Phil. I work with a homegrown audit system that does the job pretty well. There exists a single table (Audit_Log) that holds a record for every field that was modified. Here's its structure: ID (auto increment) LoginID (id of the person logged in and making the change) DateModified (datetime stamp of the modification) RecordID (the ID of the affected record in the affected table) TableName (the name of the affected table) FieldName (the name of the affected field in the affected table) OriginalValue (original value of the affected field) NewValue (new value of the affected field) Reason (reason change was made) sample record: 1,'spiowaty','2005-11-07 11:47:31.000000',19469,'tbl_220107_coverage','txt_life1_coverage_pended','2','Null','Pended Employee Life - Denied' As you may have presumed, for each field we are auditing there is a new record in the audit table. So if we're tracking changes made to 5 of the fields in a given table's record, 5 records are generated in the audit table, all with the same RecordID value. We use a custom tag sprinkled throughout our app, so anytime we want to make sure a change is audited, we feed values to that tag and it inserts records for us. Hope this helps. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:240288 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
