更新可能な台帳テーブルを作成して使用する

適用対象: SQL Server 2022 (16.x) Azure SQL データベース Azure SQL Managed Instance

この記事では、更新可能な台帳テーブルを作成する方法を示します。 次に、更新可能な台帳テーブルに値を挿入した後、データを更新します。 最後に、台帳ビューを使用して結果を表示します。 顧客の口座残高の変化を追跡する銀行のアプリケーションを例に使用します。 この例では、更新可能な台帳テーブルとそれに対応する履歴テーブルおよび台帳ビューの関係を、実際の使用に役立つよう説明します。

前提条件

• SQL Server Management Studio または Azure Data Studio。

更新可能な台帳テーブルを作成する

次のスキーマで口座残高テーブルを作成します。

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
   );
   

   Note

   データベースを作成するときに台帳データベースを有効にした場合、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';
   

   

3. 開始残高が $50 の新規顧客として、名前 Nick Jones を挿入します。

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

4. 開始残高がそれぞれ $500、$30、$200 の新しい顧客として、名前 John Smith、Joe Smith、Mary Michaels を挿入します。

   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 列には、トランザクションで値を挿入した際の順序が表示されます。

     

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 の新しい残高が示されます。

   

アクセス許可

更新可能な台帳テーブルの作成には ENABLE LEDGER 権限が必要です。 台帳テーブルに関連する権限の詳細については、権限に関する記事を参照してください。

関連するコンテンツ

• データベース台帳
• 更新可能な台帳テーブル
• 追加専用台帳テーブル
• 通常のテーブルから台帳テーブルにデータを移行する方法