Keeping track of changes in a table

Question

My colleague at work has posed me a question which I cannot answer (due to a lot of inexperience) which is related to tracking changes of relevant fields on a table.

So imagine that we have 3 tables with 20 fields each. Let's consider for this example that each of these tables has 2 fields, one named LastUpdatedOn and another named LastUpdatedBy.

If we wanted to keep track of changes in these 3 tables but only for a couple of specific fields without creating an History table for each of them containing their last version before they were updated, how could we keep track of changes on these relevant fields and still keep it generic?

Answer 1

There is no need to create an History/Audit table for each of them. You can have a single table, which stores the table and field name for the fields you want to keep track of:

audit(audit_id, table_name, field_name, action_flg, updated_by, updated_on, val_before, val_after, pk_value1, pk_value2, pk_value3, pk_value4, pk_value5).

You need to store the primary key (fields pk_value1 to pk_value5) of the table in order to uniquely identify the row that has been changed. action_flg is used if you want to keep track of updates, insertions, or rows that have been deleted. Oracle uses this table structure in some of its products.

For instance, suppose you have a table person(person_id, name, email), and you need to track changes made to the field email:

1. A new person (id=1) is created: insert into audit(1, 'person', 'email', 'A' /* add */, 'USER', '11-03-2011', null, '[email protected]', 1, null, null, null, null);

2. E-mail of person 1 is updated: insert into audit(2, 'person', 'email', 'C' /* change */, 'USER', '12-03-2011', '[email protected]', '[email protected]', 1, null, null, null, null);

3. Now suppose the e-mail of person 70 is updated: insert into audit(3, 'person', 'email', 'C' , 'USER', '12-03-2011', '[email protected]', '[email protected]', 70, null, null, null, null);

Answer 2

If you don't need to know what has changed, only that some important field has changed, then just add another timestamp column, LastImportantUpdateOn or something like that. Then add a trigger to catch edits to the fields considered "important" and write a new timestamp.

If you need to know which field changed, add a new timestamp for each field instead of one in general.