Creare e usare tabelle del libro mastro aggiornabili

  • Articolo

Si applica a: SQL Server 2022 (16.x) Database Azure SQLIstanza gestita di SQL di Azure

Questo articolo spiega come creare una tabella del libro mastro aggiornabile. Successivamente, verranno inseriti dei valori nella tabella del libro mastro aggiornabile e verranno eseguiranno aggiornamenti ai dati. Infine, verranno visualizzati i risultati usando la vista del libro mastro. Useremo un esempio di applicazione bancaria che tiene traccia dei saldi dei clienti nei propri conti. Nell'esempio viene esaminata in modo pratico la relazione tra la tabella del libro mastro aggiornabile e la tabella di cronologia corrispondente e la vista del libro mastro.

Prerequisiti

Creare una tabella del libro mastro aggiornabile

Verrà creata una tabella di saldo del conto con lo schema seguente.

Nome colonna Tipo di dati Descrizione
CustomerID int ID cliente - Chiave primaria in cluster
LastName varchar (50) Customer Last Name
FirstName varchar (50) Customer First Name
Saldo decimale (10,2) Saldo del conto

  1. Usare SQL Server Management Studio o Azure Data Studio per creare un nuovo schema e una tabella denominata [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
);

    Nota

    Specificare l'argomento LEDGER = ON è facoltativo se è stato abilitato un database del libro mastro al momento della creazione del database.

  2. Quando viene creata la tabella del libro mastro aggiornabile, vengono create anche la tabella di cronologia e la vista del libro mastro corrispondenti. Eseguire i comandi T-SQL seguenti per visualizzare la nuova tabella e la nuova vista.

    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. Inserire il nome Nick Jones come nuovo cliente con un saldo di apertura pari a $ 50.

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

  4. Inserire rispettivamente i nomi John Smith, Joe Smith e Mary Michaels come nuovi clienti con saldo di apertura pari a $ 500, $30 e $ 200.

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

  5. Visualizzare la tabella del libro mastro aggiornabile [Account].[Balance] e specificare le colonne GENERATED ALWAYS aggiunte alla tabella.

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

    Nella finestra dei risultati verranno prima visualizzati i valori inseriti dai comandi T-SQL, insieme ai metadati di sistema usati per scopi di derivazione dei dati.

    • Nella colonna ledger_start_transaction_id viene annotato l'ID di transazione univoco associato alla transazione che ha inserito i dati. Dato che John, Joe e Mary sono stati inseriti usando la stessa transazione, condividono lo stesso ID di transazione.

    • Nella colonna ledger_start_sequence_number viene annotato l'ordine di inserimento dei valori dalla transazione.

      Screenshot that shows ledger table example 1.

  6. Aggiornare il saldo di Nick da 50 a 100.

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

  7. Visualizzare la vista del libro mastro [Account].[Balance], insieme alla vista di sistema del libro mastro delle transazioni per identificare gli utenti che hanno apportato le modifiche.

     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;

    Suggerimento

    È consigliabile eseguire una query sulla cronologia delle modifiche tramite la vista del libro mastro e non la tabella di cronologia.

    Il saldo del conto di Nick è stato aggiornato correttamente nella tabella del libro mastro aggiornabile in 100.
    La vista del libro mastro mostra che l'aggiornamento della tabella del libro mastro è un'operazione DELETE sulle righe originali con 50. Il saldo con un'operazione INSERT corrispondente di una nuova riga con 100 mostra il nuovo saldo per Nick.

    Screenshot that shows ledger table example 3.

Autorizzazioni

La creazione di tabelle del libro mastro aggiornabili richiede l'autorizzazione ENABLE LEDGER. Per ulteriori informazioni sulle autorizzazioni necessarie per le tabelle del libro mastro, vedere Autorizzazioni.

Contenuto correlato