Developing the journal tables to track changes

The application tables are becoming a source of information for the data warehouse solution. Changes against these tables should be recorded so that a complete audit log can be constructed.

Getting ready

In order to track changes, a journal table is needed. This table is identical to the definition of the original table, but has a few additional columns to record information on actions, the date, and the user who made the change.

How to do it...

Journal tables are useful for auditing the changes that are made to information, and can be used to roll back information, should it be required.

The journal table we will use is a track change in the customer table, as shown in the following code snippet:

CREATE TABLE "APP_CUSTOMER_JRNL"
(
"CUST_SEQ" NUMBER NOT NULL ENABLE,
"CUST_NUM" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"CUST_NAME" VARCHAR2(50 BYTE),
"CUST_ADDRESS1" VARCHAR2(50 BYTE),
"CUST_ADDRESS2" VARCHAR2(50 BYTE),
"CUST_CITY" VARCHAR2(50 BYTE),
"CUST_PCODE_ZIP...