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

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure 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

• SQL Server Management Studio o Azure Data Studio.

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].

   SQL

   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.

   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';
   

   

3. Inserte el nombre Nick Jones como un nuevo cliente con un saldo de apertura de 50 USD.

   SQL

   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.

   SQL

   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.

   SQL

   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.

     

6. Actualice el saldo de Nick de50 a 100.

   SQL

   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.

   SQL

    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.

   

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

• Libro de contabilidad de base de datos
• Tablas actualizables del libro de contabilidad
• Tablas de libro de contabilidad de solo anexión
• Migración de datos de tablas normales a tablas de libro de contabilidad