Créer et utiliser des tables de registre pouvant être mises à jour

  • Article

S’applique à : SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

Cet article explique comment créer une table de registre pouvant être mise à jour. Ensuite, vous insérerez des valeurs dans votre table de registre pouvant être mise à jour, puis mettrez les données à jour. Enfin, vous visualiserez les résultats en utilisant l’affichage du registre. Nous utiliserons l’exemple d’une application bancaire qui suit le solde des comptes de ses clients. Notre exemple vous donne un aperçu pratique de la relation entre la table de registre pouvant être mise à jour et la table d’historique correspondante et la vue du registre correspondantes.

Prérequis

Créer une table de registre pouvant être mise à jour

Nous allons créer une table de solde de compte avec le schéma suivant.

Nom de la colonne Type de données Description
CustomerID int ID client - Clé primaire en cluster
LastName varchar (50) Nom du client
FirstName varchar (50) Prénom du client
Solde decimal (10,2) Solde du compte

  1. Utilisez SQL Server Management Studio ou Azure Data Studio pour créer un schéma et une table appelés [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
);

    Remarque

    La spécification de l’argument LEDGER = ON est facultative si vous avez activé une base de données du registre lorsque vous avez créé votre base de données.

  2. Lors de la création de la table de registre pouvant être mise à jour, la table d’historique et la vue du registre correspondantes sont également créées. Exécutez les commandes T-SQL suivantes pour afficher la nouvelle table et la nouvelle vue.

    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. Insérez le nom de Nick Jones en tant que nouveau client avec un solde d’ouverture de 50 USD.

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

  4. Insérez les noms de John Smith, Joe Smith et Mary Michaels en tant que nouveaux clients avec des soldes d’ouverture de 500 USD, 30 USD et 200 USD respectivement.

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

  5. Affichez la table de registre pouvant être mise à jour [Account].[Balance] et spécifiez les colonnes GENERATED ALWAYS ajoutées à la table.

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

    Dans la fenêtre des résultats, vous verrez d’abord les valeurs insérées par vos commandes T-SQL, ainsi que les métadonnées système utilisées pour la traçabilité des données.

    • La colonne ledger_start_transaction_id indique l’ID de transaction unique associé à la transaction qui a inséré les données. Comme John, Joe et Mary ont été insérés lors de la même transaction, ils partagent le même ID de transaction.

    • La colonne ledger_start_sequence_number indique l’ordre dans lequel les valeurs ont été insérées par la transaction.

      Screenshot that shows ledger table example 1.

  6. Mettez à jour le solde de Nick de 50 à 100.

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

  7. Affichez l’affichage des registres [Account].[Balance], ainsi que l’affichage système des registres de transactions pour identifier les utilisateurs qui ont apporté les modifications.

     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;

    Conseil

    Nous vous recommandons d’interroger l’historique des modifications à l’aide de la vue du registre et non de la table d’historique.

    Le solde du compte de Nick a été mis à jour avec succès dans la table de registre pouvant être mise à jour avec la valeur 100.
    La vue du registre indique que la mise à jour de la table du registre est une opération DELETE sur la ligne d’origine contenant 50. Le solde avec l’opération INSERT correspondante d’une nouvelle ligne contenant 100 montre le nouveau solde de Nick.

    Screenshot that shows ledger table example 3.

Autorisations

La création de tables de registre pouvant être mises à jour nécessite l’autorisation ENABLE LEDGER . Pour plus d’informations sur les autorisations associées aux tables du registre, consultez Autorisations.

Contenu connexe