Recently I was working on a solution where there was a requirement to implement the audit trail feature. An audit trail is a security-relevant chronological record, set of records, and/or source/destination of records that provide documentary evidence of the sequence of activities that have affected at any time a specific operation, procedure, event, or device.
After going through the available options and comparing each one’s pros and cons, we have settled upon a SQL Server feature called Temporal Tables.
What are Temporal Tables?
Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time. This database feature was made available from SQL Server 2016 onwards.
A system-versioned temporal table is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (that is, the database engine).
Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record the period of validity for each row, whenever a row is modified. The main table that stores current data is referred to as the current table, or simply as the temporal table.
In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema, called the history table. The system uses the history table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. During temporal table creation, users can specify an existing history table (which must be schema compliant) or let the system create a default history table.
I you are planning to make use of Temporal Tables in .NET Core, this feature was made accessible from EF Core 6.0. The feature supports:
- The creation of temporal tables using EF Core migrations
- Transformation of existing tables into temporal tables, again using migrations
- Querying historical data
- Restoring data from some point in the past
