Using a system-period temporal table for tracking auditing information

An audit trail of the changes that are made to the system-period temporal table can be made more informative with the addition of one or more generated expression columns.

Some examples of auditing information that can be tracked are

when was data modified,
who modified the data
what SQL operation modified the data.

To track when data was modified, define the table as a system-period temporal table. To track who and what SQL statement modified the data, use a generated expression column. For more information about available generated expression columns, see CREATE TABLE.

In the following example, the POLICY_INFO table contains two extra columns. AUDIT_USER tracks who modified the data by using the SESSION_USER special register. AUDIT_OP tracks the SQL operation that modified the data by using the DATA CHANGE OPERATION.

 CREATE TABLE policy_info 
  (policy_id  CHAR(4) NOT NULL,
   coverage   INT NOT NULL,
   audit_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER),
   audit_op   CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
   sys_start  TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
   sys_end    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
   ts_id      TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
   PERIOD SYSTEM_TIME (sys_start, sys_end) );

CREATE TABLE hist_policy_info LIKE policy_info;

ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info
  ON DELETE ADD EXTRA ROW;

Each row in the system temporal table and its corresponding history table tracks whether the row was changed by an insert, update, or delete operation and the name of the user that last modified the row.

ON DELETE ADD EXTRA ROW

If the ON DELETE ADD EXTRA ROW clause is specified on the ALTER TABLE ADD VERSIONING statement, an extra row is inserted into the history table when a row is deleted from a system-period temporal table. This additional row contains information about the delete operation itself.

The following example illustrates the value of ON DELETE ADD EXTRA ROW. This example shows a series of three transactions. In June of 2014, USER1 inserts a row into the POLICY_INFO table. Then, in January of 2015, USER2 updates the row. Finally, in August of 2015, USER3 deletes the row:

INSERT INTO policy_info (policy_id, coverage) 
  VALUES ('A123',12000);

UPDATE policy_info 
  SET coverage = 25000
  WHERE policy_id = 'A123'

DELETE FROM policy_info
  WHERE policy_id = ‘A123’

Without the ON DELETE ADD EXTRA ROW clause, this series of transactions would write only the following two entries to the history table. The first entry records the row as it was before USER2 made her update, and the second entry records the row as it was before USER3 did his delete. The delete operation records the row as it last existed in the temporal table. Without the ON DELETE ADD EXTRA ROW clause, there is no audit record of the delete operation itself.

Table 1. Data in the history table, `HIST_POLICY_INFO`, after the DELETE statement when `ON DELETE ADD EXTRA ROW` is not specified
`POLICY_ID`	`COVERAGE`	`AUDIT_USER`	`AUDIT_OP`	`SYS_START`	`SYS_END`	`TS_ID`
A123	12000	USER1	I	2014-06-31-22.31.33.495925	2015-01-31-22.31.33.857632	2014-06-31-22.31.33.495925
A123	25000	USER2	U	2015-01-31-22.31.33.857632	2015-08-31-22.31.33.634521	2015-01-31-22.31.33.857632

With the ON DELETE ADD EXTRA ROW clause added to the ADD VERSIONING statement, an extra row is written history table. Because the row-begin and row-end values are generated for the extra row, they are the same timestamp value. This timestamp also matches the row-end value of the first row inserted for the delete. In the following table, this additional row is shown:

Table 2. Data in the history table, `HIST_POLICY_INFO`, after the DELETE statement when `ON DELETE ADD EXTRA ROW` is specified
`POLICY_ID`	`COVERAGE`	`AUDIT_USER`	`AUDIT_OP`	`SYS_START`	`SYS_END`	`TS_ID`
A123	12000	USER1	I	2014-06-31-22.31.33.495925	2015-01-31-22.31.33.857632	2014-06-31-22.31.33.495925
A123	25000	USER2	U	2015-01-31-22.31.33.857632	2015-08-31-22.31.33.634521	2015-01-31-22.31.33.857632
A123	25000	USER3	D	2015-08-31-22.31.33.634521	2015-08-31-22.31.33.634521	2015-08-31-22.31.33.634521