Share via

Creación y uso de tablas de libro de contabilidad actualizables

  • Artículo

Se aplica a: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

En este artículo se muestra cómo crear una tabla de libro de contabilidad actualizable. A continuación, insertará valores en la tabla actualizable del libro de contabilidad y, a continuación, hará actualizaciones en los datos. Por último, verá los resultados mediante la vista de libro de contabilidad. Para ello, usaremos un ejemplo de una aplicación bancaria que hace un seguimiento del saldo de la cuenta de los clientes del banco. Este ejemplo le proporcionará una visión práctica de la relación entre la tabla actualizable del libro de contabilidad y su vista de libro de contabilidad correspondiente.

Requisitos previos

Creación de una tabla actualizable del libro de contabilidad

Crearemos una tabla del saldo de cuenta con el esquema siguiente.

Nombre de la columna Tipo de datos Descripción
CustomerID int Id. de cliente: clave principal agrupada
Apellidos varchar (50) Apellidos del cliente
Nombre varchar (50) Nombre del cliente
Saldo decimal (10,2) Saldo de la cuenta

  1. Use SQL Server Management Studio o Azure Data Studio para crear un esquema y una tabla denominados [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:

    Especificar el argumento LEDGER = ON es opcional si habilitó una base de datos de libro de contabilidad al crear la base de datos.

  2. Cuando se crea la tabla actualizable del libro de contabilidad, también se crean la tabla de historial y la vista de libro de contabilidad correspondientes. Ejecute los siguientes comandos T-SQL para ver la nueva tabla y la nueva 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. Inserte el nombre Nick Jones como un nuevo cliente con un saldo de apertura de 50 USD.

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

  4. Inserte los nombres John Smith, Joe Smith y Mary Michaels como nuevos clientes con saldos de apertura de 500 USD, 30 USD y 200 USD, respectivamente.

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

  5. Consulte la tabla actualizable del libro de contabilidad [Account].[Balance], y especifique las columnas GENERATED ALWAYS agregadas a la tabla.

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

    En la ventana de resultados, primero verá los valores que insertaron los comandos de T-SQL, junto con los metadatos del sistema que se usan con fines de linaje de datos.

    • La columna ledger_start_transaction_id indica el identificador de transacción único asociado a la transacción que insertó los datos. Como John, Joe y Mary se insertaron mediante la misma transacción, comparten el mismo identificador de transacción.

    • La columna ledger_start_sequence_number indica el orden mediante el cual la transacción insertó los valores.

      Screenshot that shows ledger table example 1.

  6. Actualice el saldo de Nick de50 a 100.

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

  7. Vea la vista de libro de contabilidad [Account].[Balance], junto con la vista del sistema de libro de contabilidad de transacciones para identificar a los usuarios que realizaron los cambios.

     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;

    Sugerencia

    Se recomienda consultar el historial de cambios a través de la vista de libro de contabilidad y no de la tabla de historial.

    El saldo de la cuenta de Nick se ha actualizado correctamente a 100 en la tabla actualizable del libro de contabilidad.
    La vista del libro de contabilidad muestra que la actualización de la tabla del libro de contabilidad es una DELETE de la fila original con 50. El saldo con la operación INSERT correspondiente de una nueva fila con 100 muestra el nuevo saldo de Nick.

    Screenshot that shows ledger table example 3.

Permisos

Para crear tablas actualizables del libro de contabilidad necesita obtener el permiso ENABLE LEDGER. Para más información sobre los permisos relacionados con las tablas de libro de contabilidad, consulte Permisos.

Contenido relacionado