Skip to content

ADR 5: Temporal tables for auditing

Status

Proposed

Context

A row-level change history is desired in PayCenter applications.

Decision

The traditional approach is to use custom audit tables that reflect the structure of the tracked tables along with triggers to perform the inserts. Temporal tables provide this as a built-in feature. MS even provides guidelines on migrating from an existing trigger solution.

Benefits over CDC - Data stays in the database rather than the log. - Temporal tables are tuned for a historical view of data changes. 

SELECT * FROM Employee
  FOR SYSTEM_TIME
    BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'
      WHERE EmployeeID = 1000 ORDER BY ValidFrom;

  • A record is not added for an INSERT

Consequences

Schema will need to be adjusted for each table in the application.

Temporal tables do not provide the user that performed the modification, however this can be addressed via table design.