更新可能な台帳テーブルを作成して使用する
適用対象: SQL Server 2022 (16.x)
Azure SQL データベース
Azure SQL Managed Instance
この記事では、更新可能な台帳テーブルを作成する方法を示します。 次に、更新可能な台帳テーブルに値を挿入した後、データを更新します。 最後に、台帳ビューを使用して結果を表示します。 顧客の口座残高の変化を追跡する銀行のアプリケーションを例に使用します。 この例では、更新可能な台帳テーブルとそれに対応する履歴テーブルおよび台帳ビューの関係を、実際の使用に役立つよう説明します。
前提条件
更新可能な台帳テーブルを作成する
次のスキーマで口座残高テーブルを作成します。
列名 | データ型 | 説明 |
---|---|---|
顧客 ID | int | 顧客 ID - クラスター化された主キー |
姓 | varchar (50) | 顧客の姓 |
FirstName | varchar (50) | 顧客の名 |
残高 | decimal (10,2) | 口座残高 |
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
引数の指定は省略できます。更新可能な台帳テーブルを作成すると、これに対応する履歴テーブルと台帳ビューも作成されます。 次の 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';
開始残高が $50 の新規顧客として、名前
Nick Jones
を挿入します。INSERT INTO [Account].[Balance] VALUES (1, 'Jones', 'Nick', 50);
開始残高がそれぞれ $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);
更新可能な台帳テーブル
[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
列には、トランザクションで値を挿入した際の順序が表示されます。
Nick
の残高を50
から100
に更新します。UPDATE [Account].[Balance] SET [Balance] = 100 WHERE [CustomerID] = 1;
[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
権限が必要です。 台帳テーブルに関連する権限の詳細については、権限に関する記事を参照してください。