Free SQL Help for Devs, DBAs, and the Curious

In the ever-evolving world of data management and database administration, maintaining an accurate and immutable audit trail is crucial. With the help of Ledger tables in SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse, creating an unchangeable record of all transactions performed in a database is now easier and more reliable. Ledger tables ensure that your audit trail is not only complete but also resistant to any form of modification.

In SQL Server 2019, using Ledger tables for audit trails is a breeze. Ledger tables are a type of system-versioned temporal table that records the history of all data in a table. When a change is made to a row, a new row is inserted into the Ledger table with the old data, providing a complete history of all changes made. To illustrate, creating a Ledger table in SQL Server 2019 would look something like the following:

CREATE TABLE LedgerTable
(
  ID int PRIMARY KEY,
  Name varchar(100),
  SysStartTime datetime2 GENERATED ALWAYS AS ROW START,
  SysEndTime datetime2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LedgerTableHistory));

The same kind of audit trail can be achieved in MySQL 8.0 using triggers. A trigger is a stored procedure that automatically initiates an action when a specific event occurs. When a change is made to a table, the trigger is activated, and the old data is stored in a separate Ledger table. Here is an example of how to create a trigger in MySQL 8.0:

CREATE TRIGGER ledger_trigger
BEFORE UPDATE ON table_name
FOR EACH ROW
  INSERT INTO LedgerTable VALUES (OLD.column1, OLD.column2, NOW());

DB2 11.5 also supports ledger tables with its temporal table feature. Temporal tables in DB2 11.5 can be used to track all changes made to a table, providing an audit trail of all transactions. Here's an example of how to create a temporal table in DB2 11.5:

CREATE TABLE LedgerTable
(
  ID INT NOT NULL,
  Name VARCHAR(100) NOT NULL,
  SysStartTime TIMESTAMP(12) NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
  SysEndTime TIMESTAMP(12) GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
)
PERIOD SYSTEM_TIME (SysStartTime, SysEndTime);

In Azure SQL and Azure Synapse, the temporal table feature is also available. It allows maintaining an audit trail of data changes over time. The way to create a temporal table in Azure SQL and Azure Synapse is very similar to that of SQL Server 2019. It uses the same SYSTEM_VERSIONING syntax to create a system-versioned temporal table that maintains a history of changes.

In conclusion, ledger tables provide a powerful tool for maintaining immutable audit trails in SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse. By using the inherent features of these database systems, we can ensure the integrity and reliability of our data history, contributing to a sound and secure data management strategy.