`OUTPUT`, `inserted` and `deleted`: Observe modified rows

After inserting, updating, or deleting data, we often need to know which rows were actually affected. OUTPUT returns values from the logical images inserted and deleted as part of the same statement. A logical image is a temporary representation of rows before or after the change; it is not a separately stored table.

inserted represents the new values; deleted, the previous ones. On a UPDATE we can query both to see the change.

Retrieve an inserted identity

INSERT INTO dbo.Categoria
(
    Nombre,
    Activa
)
OUTPUT
    inserted.IdCategoria,
    inserted.Nombre
VALUES
(
    N'Accesorios',
    1
);

The result comes from the inserted row and avoids subsequently running a separate search to retrieve its values.

Compare before and after values

In this example, ROUND(expresión, 2) rounds the new price to two decimal places. OUTPUT does not perform this calculation: it only allows observing the previous value and the result produced by UPDATE.

UPDATE dbo.Producto
SET Precio = ROUND(Precio * 1.05, 2)
OUTPUT
    inserted.IdProducto,
    deleted.Precio AS PrecioAnterior,
    inserted.Precio AS PrecioNuevo
WHERE IdCategoria = 2
  AND Activo = 1;

Each updated row produces an output row. We should not assume a certain order for that result.

Capture the output in a table

DECLARE @Cambios TABLE
(
    IdProducto INT,
    PrecioAnterior DECIMAL(12, 2),
    PrecioNuevo DECIMAL(12, 2)
);

UPDATE dbo.Producto
SET Precio = ROUND(Precio * 1.05, 2)
OUTPUT
    inserted.IdProducto,
    deleted.Precio,
    inserted.Precio
INTO @Cambios
(
    IdProducto,
    PrecioAnterior,
    PrecioNuevo
)
WHERE IdCategoria = 2
  AND Activo = 1;

SELECT
    IdProducto,
    PrecioAnterior,
    PrecioNuevo
FROM @Cambios
ORDER BY IdProducto;

OUTPUT ... INTO lets us use the changes in later steps of the same batch, the group of statements sent together to SQL Server. It can support controlled auditing—a record of what changed and when—integration, or verification, provided the design considers the complete transaction.

What image exists in each operation

Instruction	`inserted`	`deleted`
`INSERT`	New row	Not available
`UPDATE`	New values	Previous values
`DELETE`	Not available	Deleted row

These logical tables also appear inside triggers, code that SQL Server runs automatically for specified operations, and can contain many rows. The logic must never assume that only one row changed.

Do not confuse two uses of `OUTPUT`

The OUTPUT parameter of a procedure returns a value through a variable. The OUTPUT clause of a DML statement returns or captures affected rows. DML groups statements that modify data, such as INSERT, UPDATE, and DELETE. They share the word but solve different problems.

What should be checked

The output may contain rows even if the statement encounters an error and is subsequently rolled back. If the operation fails, we do not use those rows as a persistence commit.

We also review triggers, calculated columns, constraints, and the destination of OUTPUT ... INTO. For durable audits we need to design permissions, transactions and error handling, not limit the solution to copying rows.