Maintaining Data Change History in Oracle

For some software applications, it’s necessary to track changes made to data in order to comply with various regulatory or legal requirements. As an example, let’s say our application has an Oracle database with a table named WIDGET and the application needs to maintain a history of changes made to each record in the WIDGET table. The WIDGET table contains a simple ID key (WIDGET_ID) and a name attribute (WIDGET_NAME). It also has attributes to capture when the row is created/modified and by whom along with an INTERNAL_STATUS field which indicates whether or not the row is active (1) or deleted (0).

Next, we’ll create a history table to capture each version of each record. For the purposes of this example, the current active records always reside in the WIDGET table (INTERNAL_STATUS is set to 1). Notice that the table structure closely follows the table structure of the WIDGET table except that a new HISTORY_ID attribute has been added.

We’ll need a unique key for the HISTORY_ID field value in the HISTORY_WIDGET table, so let’s create a new sequence named HISTORY_WIDGET_SEQ.

Finally, we’ll need to create two triggers responsible for populating the HISTORY_WIDGET table whenever changes are made to the records in the WIDGET table. The trigger POSTDEL_WIDGET is executed after a record is deleted while the trigger POSTUPD_WIDGET is executed after a record is updated. With each data manipulation, the triggers create new records in the history table using the field values from the record that was deleted or updated by using the :OLD reference.

Since this is a very simple way of maintaining data history, it is important to note how the application should manipulate the data. It is the responsibility of the application to populate the ROW_MODIFIED and ROW_MODIFIED_BY attributes when updating records. More importantly, when deleting records, the application should first perform an update on the record to set the INTERNAL_STATUS_ID equal to 0 and to set the ROW_MODIFIED and ROW_MODIFIED_BY attributes. If this crucial step is not performed, then the history will not include a record capturing when a record was deleted and by whom.

Leave a Comment