Criar e usar tabelas do razão atualizáveis

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Este artigo mostra como criar uma tabela do razão atualizável. Em seguida, você inserirá valores em sua tabela do razão atualizável e, em seguida, fará atualizações nos dados. Por fim, você exibirá os resultados usando o modo de exibição do razão. Usaremos um exemplo de um aplicativo bancário que acompanha saldos bancários dos clientes nas respectivas contas. O exemplo fornecerá uma visão prática da relação entre a tabela do razão atualizável e a tabela de histórico correspondente, bem como a exibição do razão.

Pré-requisitos

Criando uma tabela do razão atualizável

Criaremos uma tabela de saldo de contas com o esquema a seguir.

Nome da coluna Tipo de dados Descrição
CustomerID INT ID do cliente – chave primária clusterizada
LastName varchar (50) Sobrenome do cliente
Nome varchar (50) Nome do cliente
Saldo decimal (10,2) Saldo da conta

  1. Use o SQL Server Management Studio ou o Azure Data Studio para criar um novo esquema e tabela chamados [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
);

    Observação

    A especificação do argumento LEDGER = ON será opcional se você tiver habilitado um banco de dados do razão quando criou o banco de dados.

  2. Quando a tabela do razão atualizável é criada, a tabela de histórico correspondente e a exibição do razão também são criadas. Execute os comandos T-SQL a seguir para ver a nova tabela e a nova exibição.

    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. Insira o nome, Nick Jones, como um novo cliente com um saldo de abertura de US$ 50.

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

  4. Insira os novos John Smith, Joe Smith e Mary Michaels como novos clientes com saldos de abertura de US$ 500, US$ 30 e US$ 200, respectivamente.

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

  5. Veja a tabela do razão atualizável [Account].[Balance], e especifique as colunas GENERATED ALWAYS adicionadas à tabela.

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

    Na janela de resultados, primeiro, você verá os valores inseridos pelos comandos T-SQL, juntamente com os metadados do sistema usados para fins de linhagem de dados.

    • A coluna ledger_start_transaction_id anota a ID de transação exclusiva associada à transação que inseriu os dados. Como John, Joe, e Mary foram inseridos usando a mesma transação, eles têm a mesma ID de transação.

    • A coluna ledger_start_sequence_number anota a ordem pela qual os valores foram inseridos pela transação.

      Screenshot that shows ledger table example 1.

  6. Atualize o saldo de Nick de 50 para 100.

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

  7. Exiba o modo de exibição do razão [Account].[Balance] com o modo de exibição do sistema de razão de transação para identificar os usuários que fizeram as alterações.

     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;

    Dica

    Recomendamos que você consulte o histórico de alterações por meio da exibição do razão, não da tabela de histórico.

    O saldo da conta de Nick foi atualizado com êxito para 100 na tabela do razão atualizável.
    A exibição do razão mostra que a atualização da tabela do razão é uma DELETE da linha original com 50. O saldo com um INSERT correspondente de uma nova linha com 100 mostra o novo saldo como Nick.

    Screenshot that shows ledger table example 3.

Permissões

A criação de tabelas do razão atualizáveis exige a permissão ENABLE LEDGER. Para obter mais informações sobre as permissões relacionadas às tabelas do razão, confira Permissões.

Conteúdo relacionado