Manejo de transacciones para insertar, modificar o eliminar en SQL Server

Resumen: en este tutorial, aprenderás sobre las transacciones de SQL Server y cómo usar T-SQL para ejecutar transacciones.

Introducción a las transacciónes de SQL Server

Una transacción es una unidad de trabajo que normalmente contiene varias instrucciones T-SQL.

Si una transacción es exitosa, los cambios se confirman en la base de datos. Sin embargo, si una transacción tiene un error, los cambios deben revertirse.

Al ejecutar una sola declaración como INSERT, UPDATE y DELETE, SQL Server usa la transacción de confirmación automática. En este caso, cada declaración es una transacción.

Para iniciar una transacción explícitamente, primero usa la instrucción BEGIN TRANSACTION o BEGIN TRAN:

BEGIN TRANSACTION;

Luego, ejecute una o más declaraciones, incluidas INSERT, UPDATE y DELETE.

Finalmente, confirme la transacción usando la instrucción COMMIT:

COMMIT;

O desaz la transacción usando la instrucción ROLLBACK:

ROLLBACK;

Aquí está la secuencia de declaraciones para iniciar una transacción explícitamente y confirmarla:

-- start a transaction
BEGIN TRANSACTION;

-- other statements

-- commit the transaction
COMMIT;

Ejemplo de transacciónes en SQL Server

Crearemos dos tablas: invoices e invoice_items para la demostración:

CREATE TABLE invoices (
  id int IDENTITY PRIMARY KEY,
  customer_id int NOT NULL,
  total decimal(10, 2) NOT NULL DEFAULT 0 CHECK (total >= 0)
);

CREATE TABLE invoice_items (
  id int,
  invoice_id int NOT NULL,
  item_name varchar(100) NOT NULL,
  amount decimal(10, 2) NOT NULL CHECK (amount >= 0),
  tax decimal(4, 2) NOT NULL CHECK (tax >= 0),
  PRIMARY KEY (id, invoice_id),
  FOREIGN KEY (invoice_id) REFERENCES invoices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

La tabla de invoices almacena el encabezado de la factura, mientras que la tabla de invoice_items almacena los elementos que incluye la factura. El campo total en la tabla de invoices se calcula a partir de los elementos de invoice_tems.

El siguiente ejemplo usa las instrucciones BEGIN TRANSACTION y COMMIT para crear una transacción

BEGIN TRANSACTION;

INSERT INTO invoices (customer_id, total)
VALUES (100, 0);

INSERT INTO invoice_items (id, invoice_id, item_name, amount, tax)
VALUES (10, 1, 'Keyboard', 70, 0.08),
       (20, 1, 'Mouse', 50, 0.08);

UPDATE invoices
SET total = (SELECT
  SUM(amount * (1 + tax))
FROM invoice_items
WHERE invoice_id = 1);

COMMIT;

En este ejemplo

Primero, se inicia una transacción explícitamente usando la instrucción BEGIN TRANSACTION:

BEGIN TRANSACTION;

A continuación, inserta una fila en la tabla de invoices y devuelve el id de la factura:

DECLARE @invoice TABLE (
  id int
);

DECLARE @invoice_id int;

INSERT INTO invoices (customer_id, total)
OUTPUT INSERTED.id INTO @invoice
VALUES (100, 0);

SELECT
  @invoice_id = id
FROM @invoice;

Luego, inserta dos filas en la tabla invoice_items:

INSERT INTO invoice_items (id, invoice_id, item_name, amount, tax)
VALUES (10, @invoice_id, 'Keyboard', 70, 0.08),
       (20, @invoice_id, 'Mouse', 50, 0.08);

Después de eso, calcula el total usando la tabla de invoice_items y actualízalo en la tabla de invoices:

UPDATE invoices
SET total = (
    SELECT SUM(amount * (1 + tax))
    FROM invoice_items
    WHERE invoice_id = @invoice_id
);

Finalmente, confirma la transacción usando la instrucción COMMIT:

COMMIT;

Resumen

• Utiliza la instrucción BEGIN TRANSACTION para iniciar una transacción de forma explícita.
• Utiliza la declaración COMMIT para confirmar la transacción y la declaración ROLLBACK para revertir la transacción.