创建和使用可更新账本表

  • 项目

适用于： SQL Server 2022 (16.x) Azure SQL 数据库Azure SQL 托管实例

本文介绍如何创建可更新账本表。 随后，你可在可更新的账本表中插入值，然后对数据进行更新。 最后，可使用账本视图查看结果。 我们将使用示例银行应用程序跟踪银行客户帐户中的余额。 我们的示例将让你实际了解可更新账本表及其相应的历史记录表和账本视图之间的关系。

先决条件

创建可更新的账本表

我们将使用以下架构创建帐户余额表。

列名称 数据类型 描述
CustomerID int 客户 ID - 聚合的主键
姓氏 varchar (50) 客户姓氏
名字 varchar (50) 客户名字
余额 decimal (10,2) 帐户余额

  1. 使用 SQL Server Management Studio 或 Azure Data Studio 创建名为 [Account].[Balance] 的新架构和表。

    CREATE SCHEMA [Account];
GO  
CREATE TABLE [Account].[Balance]
(
    [CustomerID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [LastName] VARCHAR (50) NOT NULL,
    [FirstName] VARCHAR (50) NOT NULL,
    [Balance] DECIMAL (10,2) NOT NULL
)
WITH 
(
 SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Account].[BalanceHistory]),
 LEDGER = ON
);

    注意

    如果创建数据库时已启用账本数据库，则指定 LEDGER = ON 参数是可选操作。

  2. 创建可更新的账本表时，还会创建相应的历史记录表和账本视图。 运行以下 T-SQL 命令可查看新表和新视图。

    SELECT 
ts.[name] + '.' + t.[name] AS [ledger_table_name]
, hs.[name] + '.' + h.[name] AS [history_table_name]
, vs.[name] + '.' + v.[name] AS [ledger_view_name]
FROM sys.tables AS t
JOIN sys.tables AS h ON (h.[object_id] = t.[history_table_id])
JOIN sys.views v ON (v.[object_id] = t.[ledger_view_id])
JOIN sys.schemas ts ON (ts.[schema_id] = t.[schema_id])
JOIN sys.schemas hs ON (hs.[schema_id] = h.[schema_id])
JOIN sys.schemas vs ON (vs.[schema_id] = v.[schema_id])
WHERE t.[name] = 'Balance';

    Screenshot that shows querying new ledger tables.

  3. 插入姓名 Nick Jones，作为开户余额为 50 美元的新客户。

    INSERT INTO [Account].[Balance]
VALUES (1, 'Jones', 'Nick', 50);

  4. 插入姓名 John Smith、Joe Smith 和 Mary Michaels，作为开户余额分别为 500、30 和 200 美元的新客户。

    INSERT INTO [Account].[Balance]
VALUES (2, 'Smith', 'John', 500),
(3, 'Smith', 'Joe', 30),
(4, 'Michaels', 'Mary', 200);

  5. 查看 [Account].[Balance] 可更新账本表，指定添加到该表的 GENERATED ALWAYS 列。

    SELECT [CustomerID]
   ,[LastName]
   ,[FirstName]
   ,[Balance]
   ,[ledger_start_transaction_id]
   ,[ledger_end_transaction_id]
   ,[ledger_start_sequence_number]
   ,[ledger_end_sequence_number]
 FROM [Account].[Balance];

    在结果窗口中，你将首先看到 T-SQL 命令插入的值，以及用于数据世系的系统元数据。

    • ledger_start_transaction_id 列记下与插入数据的事务相关联的唯一事务 ID。 因为 John、Joe 和 Mary 是使用同一事务插入的，因此它们共享相同的事务 ID。

    • ledger_start_sequence_number 列记下事务插入值的顺序。

      Screenshot that shows ledger table example 1.

  6. 将 Nick 的余额从 50 更新为 100。

    UPDATE [Account].[Balance] SET [Balance] = 100
WHERE [CustomerID] = 1;

  7. 查看 [Account].[Balance] 账本视图以及交易账本系统视图，标识做出更改的用户。

     SELECT
 t.[commit_time] AS [CommitTime] 
 , t.[principal_name] AS [UserName]
 , l.[CustomerID]
 , l.[LastName]
 , l.[FirstName]
 , l.[Balance]
 , l.[ledger_operation_type_desc] AS Operation
 FROM [Account].[Balance_Ledger] l
 JOIN sys.database_ledger_transactions t
 ON t.transaction_id = l.ledger_transaction_id
 ORDER BY t.commit_time DESC;

    提示

    建议通过账本视图而不是历史记录表查询更改历史记录。

    Nick 的帐户余额已在可更新账本表中成功更新为 100。
    账本视图显示，更新账本表时，对包含 50 的原始行执行 DELETE 操作。 在对包含 100 的新行执行相应的 INSERT 操作后，余额显示了 Nick 的新余额。

    Screenshot that shows ledger table example 3.

权限

创建可更新的账本表需要 ENABLE LEDGER 权限。 有关与账本表相关的权限的详细信息，请参阅权限。

相关内容