Track changes in a table

Question

My colleague at work asked me a question that I can’t answer (due to great inexperience), which is related to tracking changes in the corresponding fields on the table.

So, imagine that we have 3 tables of 20 fields. Let's consider for this example that each of these tables has 2 fields, one of which is called LastUpdatedOn, and the other is LastUpdatedBy.

If we want to track changes in these three tables, but only for a few specific fields, without creating a history table for each of them containing their latest version before updating them, how can we track changes in these corresponding fields and still keep it common?

Answer 1

There is no need to create a history / audit table for each of them. You may have a single table that stores the table name and fields for the fields that you want to track:

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 save the primary key (fields pk_value1 to pk_value5 ) of the table to uniquely identify the row that was changed. action_flg used if you want to keep track of updates, inserts or lines that have been deleted. Oracle uses this table structure in some of its products.

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

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

• Updated person 1 email: insert into audit(2, 'person', 'email', 'C' /* change */, 'USER', '12-03-2011', ' [e-mail] ', ' [e-mail] ', 1, null, null, null, null);

• Now suppose that person 70 email has been updated: insert into audit(3, 'person', 'email', 'C' , 'USER', '12-03-2011', ' [e-mail] ', ' [e-mail] ', 70, null, null, null, null);

Answer 2

If you don’t need to know what has changed, only the fact that some important field has changed has changed, then just add another timestamp column, LastImportantUpdateOn or something like that. Then add a trigger to make changes to the fields that are considered “important” and write a new timestamp.

If you need to know which field has been changed, add a new timestamp for each field instead of the common one.