建立和使用可更新的總帳資料表

  • 發行項

適用於： SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL 受控執行個體

本文示範如何建立可更新的總帳資料表。 接下來，您會在可更新的總帳資料表中插入值，然後對資料進行更新。 最後，您會使用總帳檢視來檢視結果。 我們將以一個銀行應用程式為例，該應用程式可追蹤銀行客戶帳戶中的餘額。 我們的範例將可讓您實際查看可更新的總帳資料表與其對應的記錄資料表和總帳檢視之間的關聯性。

必要條件

建立可更新的總帳資料表

我們將建立具有下列結構描述的帳戶餘額資料表。

資料行名稱 資料類型 描述
CustomerID int 客戶識別碼 - 已叢集處理的主索引鍵
姓氏 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 資料行會註明與插入資料之交易相關聯的唯一交易識別碼。 由於 John、Joe 和 Mary 都是使用相同的交易來插入，因此會共用相同的交易識別碼。

    • 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。
    總帳檢視會顯示總帳資料表的更新作業是 DELETE 具有 50 的原始資料列。 對應 INSERT 具有 100 之新資料列的餘額會顯示 Nick 的新餘額。

    Screenshot that shows ledger table example 3.

權限

建立可更新的總帳資料表需要 ENABLE LEDGER (啟用總帳) 權限。 如需總帳資料表相關權限的詳細資訊，請參閱權限 (部分機器翻譯)。

相關內容