Clausula OUTPUT en SQL SERVER

Generalmente, uno no espera que una sentencia de modificación haga más que modificar datos. Es decir, no esperamos que una sentencia de modificación retorne un resultados. Sin embargo, en algunos escenarios el poder recuperar datos de registros modificados puede ser útil.

Hasta el día de hoy, y desde versiones anteriores de SQL Server, los triggers nos brindan la posibilidad de acceder a las tablas virtuales deleted e inserted en las cuales, solamente durante la ejecución del trigger se podía recuperar, generalmente para razones de auditoría, los registros que eran eliminados, modificados o insertados. Bien, desde SQL Server 2005, existe la opción de agregar una cláusula OUTPUT que nos permite acceder a estas tablas virtuales deleted e inserted en operaciones que no necesariamente son triggers sino simples operaciones DML (Data Manipulation Language).

Cabe recordar que para una operación DELETE la tabla que se poblará será la tabla deleted, para una operación INSERT, la tabla inserted y para una operación UPDATE, las tablas deleted e inserted. La cláusula OUTPUT retornará los atributos requeridos de las filas modificadas como un conjunto de resultados, de manera muy similar a cómo lo hace una sentencia SELECT. Si se quiere direccionar el conjunto de resultados a una table, es necesario agregar la cláusula INTO junto con el nombre de la tabla destino. Sin más preámbulos, les dejamos tres ejemplos prácticos y de aplicación directa de esta cláusula:

 

OUTPUT con DELETE

–Declaramos variables de tipo tabla, para los datos y para almacenar el resultado de OUTPUT
DECLARE @table1 table(Id int identity(1,1), Nombre varchar(100))
DECLARE @table2 table(Id int, Nombre varchar(100), FechaProceso smalldatetime)

–Insertamos datos en la primera variable de tipo tabla
INSERT INTO @table1 (Nombre) VALUES
(‘fila 1’),
(‘fila 2’),
(‘fila 3’)

–Verificamos la inserción de datos en la primera variable de tipo tabla
SELECT Id, Nombre FROM @table1

–Eliminamos datos y los registros originales los almacenamos en la segunda tabla
–incluimos la función GETDATE() para capturar la fecha del evento
DELETE FROM @table1
OUTPUT deleted.Id, deleted.Nombre, GETDATE() into @table2
WHERE id in (1,2)

–Verificamos la inserción de datos en la segunda variable de tipo tabla,
–la cual cumple la función de tabla de auditoria
SELECT Id, Nombre, FechaProceso FROM @table2

 

OUTPUT con INSERT

–Declaramos variables de tipo tabla, para los datos originales, para realizar la inserción
–de los datos originales y para almacenar el resultado de OUTPUT

DECLARE @table1 table(Id int identity(1,1), Nombre varchar(100))
DECLARE @table2 table(Id int, Nombre varchar(100))
DECLARE @table3 table(Id int, Nombre varchar(100), FechaProceso smalldatetime)

–Insertamos datos en la primera variable de tipo tabla
INSERT INTO @table1 (Nombre) VALUES
(‘fila 1’),
(‘fila 2’),
(‘fila 3’)

–Verificamos la inserción de datos en la primera variable de tipo tabla
SELECT Id, Nombre FROM @table1

–Insertamos datos desde la primera variable de tipo tabla hacia la segunda tabla
–y los registros insertados los almacenamos en la tercera variable de tipo tabla
–Incluimos la función GETDATE() para capturar la fecha del evento

INSERT INTO @table2
OUTPUT inserted.Id, inserted.Nombre, GETDATE() INTO @table3
SELECT Id, Nombre FROM @table1
WHERE id in (1,2)

–Verificamos la inserción de datos en la segunda variable de tipo tabla,
–la cual cumple la función de tabla de auditoria 

SELECT Id, Nombre, FechaProceso FROM @table3

 

OUTPUT con UPDATE

–Declaramos variables de tipo tabla, para los datos y para almacenar el resultado de OUTPUT
DECLARE @table1 table(Id int identity(1,1), Nombre varchar(100))
DECLARE @table2 table(Nombre_Old varchar(100), Nombre_New varchar(100), FechaProceso smalldatetime)

–Insertamos datos en la primera variable de tipo tabla
INSERT INTO @table1 (nombre) VALUES
(‘fila 1’),
(‘fila 2’),
(‘fila 3’)

–Verificamos la inserción de datos en la primera variable de tipo tabla
SELECT Id, Nombre from @table1

–Modificamos datos y los registros originales los almacenamos en la segunda tabla,
–Incluimos la función GETDATE() para capturar la fecha del evento

UPDATE @table1
SET Nombre = ‘fila new 1’
OUTPUT deleted.Nombre, inserted.Nombre, GETDATE() INTO @table2
WHERE id = 1

–Verificamos la inserción de datos en la segunda variable de tipo tabla,
–la cual cumple la función de tabla de auditoria 

SELECT Nombre_Old, Nombre_New, FechaProceso FROM @table2

 

De esta manera hemos visto cómo SQL Server nos brinda una gran flexibilidad para el control de cambios incluso sin el uso de triggers sino solamente agregando cláusulas a las sentencias DML existentes. Para conocer cómo usar OUTPUT con la sentencia MERGE consultar el artículo Fusionando Datos con la Sentencia MERGE

 

Un comentario en “Clausula OUTPUT en SQL SERVER

Deja un comentario

Tu dirección de correo electrónico no será publicada.