Создание и использование обновляемых таблиц реестра

  • Статья

Область применения: SQL Server 2022 (16.x) База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье показано, как создать обновляемую таблицу реестра. После чего необходимо вставить значения в обновляемую таблицу реестра, а затем внести изменения в данные. Наконец, можно просмотреть результаты с помощью представления реестра. Мы будем использовать пример, в котором банковское приложение отслеживает остаток на счетах клиентов. Наш пример поможет практически представить взаимоотношения между обновляемой таблицей реестра, соответствующей таблицей журнала и представлением реестра.

Необходимые компоненты

Создание обновляемой таблицы реестра

Мы создадим таблицу для остатка на счете со следующей схемой.

Имя столбца Тип данных Description
CustomerID INT Идентификатор клиента — кластеризованный первичный ключ
LastName varchar(50) Фамилия клиента
FirstName 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 разрешение. Подробные сведения о разрешениях, связанных с таблицами реестра, см. в разделе Разрешения.

Связанный контент