March 10, 2005 at 2:07 am
For our main tables, when a row update is made, a trigger stores the record before and after in an 'updated_...' table. However this is very difficult to report on (i.e. who did what and when) without running a batch program to process the data first. Is there a better way of storing data on updates?
March 10, 2005 at 6:15 am
Can you post your current DDL for both tables. I need to understand what you have. For me I have a column for last modified by and a last modified date in the main table, in the audit table I have all the same fields but add an audit entry date which allows me to link easier to the next change thru the history by using it.
March 10, 2005 at 6:29 am
yes, we also store 'amended_by' and amended_date' on the main tables.
e.g.
the contact table has:
id (primary key), name, address, ..., entered_by, entry_date, amended_by, amended_date.
the updated_contact table has:
log_id (primary key), id, name, address, ..., entered_by, entry_date, amended_by, amended_date., log_date, newdata
with newdata=0 for the 'before' record and =1 for the 'after' record.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply