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).
CREATE TABLE WIDGET ( WIDGET_ID NUMBER(10), WIDGET_NAME VARCHAR2(150), INTERNAL_STATUS NUMBER(10), ROW_ENTERED DATE, ROW_ENTERED_BY VARCHAR2(15), ROW_MODIFIED DATE, ROW_MODIFIED_BY VARCHAR2(15) ) /
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.
CREATE TABLE HISTORY_WIDGET ( HISTORY_ID NUMBER(10), WIDGET_ID NUMBER(10), WIDGET_NAME VARCHAR2(150), INTERNAL_STATUS NUMBER(10), ROW_ENTERED DATE, ROW_ENTERED_BY VARCHAR2(15), ROW_MODIFIED DATE, ROW_MODIFIED_BY VARCHAR2(15) ) /
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.
CREATE SEQUENCE HISTORY_WIDGET_SEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER /
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.
CREATE OR REPLACE TRIGGER POSTDEL_WIDGET
AFTER DELETE ON WIDGET
FOR EACH ROW
BEGIN
INSERT INTO HISTORY_WIDGET
(HISTORY_ID,
WIDGET_ID,
WIDGET_NAME,
INTERNAL_STATUS,
ROW_ENTERED,
ROW_ENTERED_BY,
ROW_MODIFIED,
ROW_MODIFIED_BY)
VALUES (HISTORY_WIDGET_SEQ.NEXTVAL,
:OLD.WIDGET_ID,
:OLD.WIDGET_NAME,
:OLD.INTERNAL_STATUS,
:OLD.ROW_ENTERED,
:OLD.ROW_ENTERED_BY,
:OLD.ROW_MODIFIED,
:OLD.ROW_MODIFIED_BY);
END;
/
CREATE OR REPLACE TRIGGER POSTUPD_WIDGET
AFTER UPDATE ON WIDGET
FOR EACH ROW
BEGIN
INSERT INTO HISTORY_WIDGET
(HISTORY_ID,
WIDGET_ID,
WIDGET_NAME,
INTERNAL_STATUS,
ROW_ENTERED,
ROW_ENTERED_BY,
ROW_MODIFIED,
ROW_MODIFIED_BY)
VALUES (HISTORY_WIDGET_SEQ.NEXTVAL,
:OLD.WIDGET_ID,
:OLD.WIDGET_NAME,
:OLD.INTERNAL_STATUS,
:OLD.ROW_ENTERED,
:OLD.ROW_ENTERED_BY,
:OLD.ROW_MODIFIED,
:OLD.ROW_MODIFIED_BY);
END;
/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.