Create and use append-only ledger tables
Applies to: SQL Server 2022 (16.x) Azure SQL Database
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.
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|
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));
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');
SELECT * ,[ledger_start_transaction_id] ,[ledger_start_sequence_number] FROM [AccessControl].[KeyCardEvents];
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;
Try to update the
KeyCardEventstable by changing the
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.
Creating append-only ledger tables requires the
ENABLE LEDGER permission. For more information on permissions related to ledger tables, see Permissions.