En el post anterior traté de explicar como utilizar la instrucción MERGE para realizar tanto la inserción como la modificación e incluso la eliminación en una sola instrucción/estructura relacional.
Ahora, continuando con el mismo ejemplo, que sucedería si nos piden registrar un histórico de los cambios de existencias…
Pues bien la misma estructura MERGE nos proporciona un mecanismo mediante el cual tomar la salida de la misma, con los registros insertados y modificados, e insertarlos en otra tabla.
Comencemos con crear una estructura mediante la cual guardar nuestro histórico de existencias:CREATE TABLE ProductoHistorico
(
conProductoHistorico INT NOT NULL IDENTITY (1,1),
conProducto INT NOT NULL
CONSTRAINT ProductoHsitorico_Producto_FK FOREIGN KEY
REFERENCES Producto(conProducto),
numCantidadAnterior INT NULL,
numCantidadNueva INT NULL
CONSTRAINT ProductoHistorico_numCantidadNueva_DF DEFAULT (0),
fecModificacion DATETIME NOT NULL
CONSTRAINT ProductoHistorico_fecModificacion DEFAULT (GETDATE()),
usrModifico VARCHAR(10) NOT NULL
CONSTRAINT ProductoHistorico_PK
PRIMARY KEY CLUSTERED (conProductoHistorico)
WITH (IGNORE_DUP_KEY = OFF)
);
Es simple, tenemos un consecutivo, una llave foránea con la tabla de productos, la cantidad anterior, la cantidad nueva, la fecha y el usuario que realizó la modificación.
Intentamos modificar el procedimiento almacenado de esta manera:
CREATE PROCEDURE spCargaProductosHistorico(@pusrModifico varchar(10))
AS
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO ProductoHistorico
(conProducto,numCantidadAnterior, numCantidadNueva,fecModificacion, usrModifico)
SELECT conProducto,numCantidadAnterior, numCantidad,fecModificacion, usrModifico
FROM
(MERGE Producto WITH (HOLDLOCK) AS TGT
USING (SELECT PC.cocProducto, desProducto, numCantidad
FROM productoCarga PC WITH (HOLDLOCK))
AS SRC (cocProducto, desProducto, numCantidad)
ON (TGT.cocProducto = SRC.cocProducto)
WHEN MATCHED THEN
UPDATE SET TGT.desProducto = src.desProducto,
TGT.numCantidad = (TGT.numCantidad + SRC.numCantidad)
WHEN NOT MATCHED THEN
INSERT (cocProducto, desProducto, numCantidad, fecIngreso)
VALUES (SRC.cocProducto, SRC.desProducto, SRC.numCantidad, GETDATE())
OUTPUT INSERTED.conProducto, COALESCE(DELETED.numCantidad,0) AS numCantidadAnterior,
INSERTED.numCantidad, GETDATE() as fecModificacion, @pusrModifico AS usrModifico)
AS HIST (conProducto, numCantidadAnterior,numCantidad,fecModificacion, usrModifico);
TRUNCATE TABLE productoCarga
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Expliquemos un poco: la cláusula OUTPUT nos devuelve dos tablas virtuales: INSERTED y DELETED, las mismas que están disponibles en los triggers, la primera corresponde con las filas insertadas (o a las modificadas con los nuevos valores) y la segunda a los valores eliminados o reemplazados. Además, no lo utilizamos en este caso, pero también devuelve un valor especial en la variable $Action que puede contener uno de los siguientes valores INSERT, UPDATE, DELETE, en función de la acción realizada en cada fila. Ahora bien, utilizamos estas salidas de la cláusula OUTPUT para insertarlas en la tabla ProductoHistorico por medio de un INSERT SELECT FROM.
Pero hay un problema, si tratamos de compilar nuestro procedimiento almacenado nos cuenta que se nos presenta un error.
The target table 'ProductoHistorico' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'ProductoHsitorico_Producto_FK'.
El mensaje es fácil de entender: no podemos poner una tabla como receptora de los datos de un MERGE si tiene una Foreign Key (FK, llave Foránea) con la tabla destino del MERGE. El hecho de que sea fácil de entender no significa que nos hayamos percatado del problema antes.
Ahora tenemos varias opciones: una sería eliminar la FK con la tabla de productos, pero perderíamos la integridad referencial, que puede que no ser malo, de hecho puede ser necesario si queremos guardar las eliminaciones físicas de los productos. Otra sería, aparte de la eliminación de la COSNTRAINT de la FK, eliminar el campo conProducto propiamente de la tabla de histórico y añadir el código de producto sin FK, que al fin y al cabo es el que verdaderamente importaría en una tabla de históricos; esta parece ser la más acertada y conveniente en un escenario real. Sin embargo hay otra opción, que para fines didácticos, es la que voy implementar: guardar el resultado del MERGE en una variable tipo tabla y luego insertarla finalmente en la histórica.
Veamos como quería esta segunda aproximación de nuestro procedimiento almacenadoCREATE PROCEDURE spCargaProductosHistorico(@pusrModifico varchar(10))
AS
SET NOCOUNT ON;
BEGIN TRANSACTION
BEGIN TRY
DECLARE @TablaTemp Table (
conProducto INT NOT NULL,
numCantidadAnterior INT NULL,
numCantidadNueva INT NULL DEFAULT (0),
fecModificacion DATETIME NOT NULL DEFAULT (GETDATE()),
usrModifico VARCHAR(10) NOT NULL
)
INSERT INTO @TablaTemp
(conProducto,numCantidadAnterior, numCantidadNueva,fecModificacion, usrModifico)
SELECT conProducto,numCantidadAnterior, numCantidad,fecModificacion, usrModifico
FROM
(MERGE Producto WITH (HOLDLOCK) AS TGT
USING (SELECT PC.cocProducto, desProducto, numCantidad
FROM productoCarga PC WITH (HOLDLOCK))
AS SRC (cocProducto, desProducto, numCantidad)
ON (TGT.cocProducto = SRC.cocProducto)
WHEN MATCHED THEN
UPDATE SET TGT.desProducto = src.desProducto,
TGT.numCantidad = (TGT.numCantidad + SRC.numCantidad)
WHEN NOT MATCHED THEN
INSERT (cocProducto, desProducto, numCantidad, fecIngreso)
VALUES (SRC.cocProducto, SRC.desProducto, SRC.numCantidad, GETDATE() )
OUTPUT INSERTED.conProducto, COALESCE(DELETED.numCantidad,0) AS numCantidadAnterior,
INSERTED.numCantidad, GETDATE() as fecModificacion, @pusrModifico AS usrModifico)
AS HIST (conProducto, numCantidadAnterior,numCantidad,fecModificacion, usrModifico);
INSERT INTO ProductoHistorico
(conProducto,numCantidadAnterior, numCantidadNueva, fecModificacion, usrModifico)
SELECT conProducto,numCantidadAnterior, numCantidadNueva,fecModificacion, usrModifico
FROM @TablaTemp
TRUNCATE TABLE productoCarga
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Simplemente “vaciamos” el resultado del MERGE en la variable tabla y luego está en la tabla ProductoHistorico. Ahora si nos compila.
Y para probarloINSERT INTO ProductoCarga
([cocProducto], [desProducto],[numCantidad])
VALUES ('AA001','Produto de Prueba 1',33),
('AA002','Produto de Prueba 2',17),
('AA003','Produto de Prueba 3',21),
('AA004','Produto de Prueba 4',33),
('AA005','Produto de Prueba 5',44),
('AA006','Produto de Prueba 6',20),
('AA007','Produto de Prueba 7',20),
('AA008','Produto de Prueba 8',60),
('AA009','Produto de Prueba 9',80)
SELECT * FROM ProductoCarga
SELECT * FROM Producto
--EXECUTE spCargaProductos
DECLARE @pusrModifico VARCHAR(10)='FOY'
EXECUTE spCargaProductosHistorico @pusrModifico
SELECT * FROM ProductoCarga
SELECT * FROM Producto
SELECT * FROM ProductoHistorico
Todo funciona de manera adecuada
Igual que en el post anterior señalar que la cláusula MERGE no es la panacea ni la pomada canaria, recalcar que es necesario probar nuestros escenarios para no tener sobresaltos en nuestras puestas en producción, principalmente cuando haya triggers de pormedio.
Clausula MERGE
Problemas de la clausula MERGE
