Reporting on Row Updates

  • 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?

  • 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.

  • 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