Edit

Share via

Create and use append-only ledger tables

  • Article

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

This article shows you how to create an append-only ledger table. Next, you'll insert values in your append-only ledger table and then attempt to make updates to the data. Finally, you'll view the results by using the ledger view. We'll use an example of a card key access system for a facility, which is an append-only system pattern. Our example will give you a practical look at the relationship between the append-only ledger table and its corresponding ledger view.

For more information, see Append-only ledger tables.

Prerequisites

Create an append-only ledger table

We'll create a KeyCardEvents table with the following schema.

Column name Data type Description
EmployeeID int The unique ID of the employee accessing the building
AccessOperationDescription nvarchar (MAX) The access operation of the employee
Timestamp datetime2 The date and time the employee accessed the building

  1. Use SQL Server Management Studio or Azure Data Studio to create a new schema and table called [AccessControl].[KeyCardEvents].

    CREATE SCHEMA [AccessControl];
GO
CREATE TABLE [AccessControl].[KeyCardEvents]
   (
      [EmployeeID] INT NOT NULL,
      [AccessOperationDescription] NVARCHAR (1024) NOT NULL,
      [Timestamp] Datetime2 NOT NULL
   )
   WITH (LEDGER = ON (APPEND_ONLY = ON));

  2. Add a new building access event in the [AccessControl].[KeyCardEvents] table with the following values.

    INSERT INTO [AccessControl].[KeyCardEvents]
VALUES ('43869', 'Building42', '2020-05-02T19:58:47.1234567');

  3. View the contents of your KeyCardEvents table, and specify the GENERATED ALWAYS columns that are added to your append-only ledger table.

    SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents];

    Screenshot that shows results from querying the KeyCardEvents table.

  4. View the contents of your KeyCardEvents ledger view along with the ledger transactions system view to identify who added records into the table.

     SELECT
 t.[commit_time] AS [CommitTime] 
 , t.[principal_name] AS [UserName]
 , l.[EmployeeID]
 , l.[AccessOperationDescription]
 , l.[Timestamp]
 , l.[ledger_operation_type_desc] AS Operation
 FROM [AccessControl].[KeyCardEvents_Ledger] l
 JOIN sys.database_ledger_transactions t
 ON t.transaction_id = l.ledger_transaction_id
 ORDER BY t.commit_time DESC;

  5. Try to update the KeyCardEvents table by changing the EmployeeID from 43869 to 34184.

    UPDATE [AccessControl].[KeyCardEvents] SET [EmployeeID] = 34184;

    You'll receive an error message that states the updates aren't allowed for your append-only ledger table.

    Screenshot that shows the append-only error message.

Permissions

Creating append-only ledger tables requires the ENABLE LEDGER permission. For more information on permissions related to ledger tables, see Permissions.

Related content