Triggers – Historial de registros eliminados

Trigger historial de eliminados

Los Triggers son una herramienta muy poderosa para asegurar integridad de datos, posibilidad de recuperar los datos si se usa una eliminación usando Delete, guardar historial de acciones para efectos de auditoria (Ver Historial con Triggers),  acciones que pueden reemplazar a la inserción  (Ver Triggers Instead of), etc.

Eliminación de registros

La eliminación de registros de una tabla se puede hacer usando la instrucción Delete (Ver Eliminación de registros), lo que en ocasiones no pueda ejecutarse debido a restricciones de tipo Foreign Key (Ver Claves
Foráneas).
Un registro eliminado con Delete es imposible de recuperar, se recomienda el borrado lógico, es decir,  usando un campo que puede ser de tipo caracter donde se pueda guardar datos como A de Activo y E de Eliminado.



Como crear un historial de registros eliminados

En este artículo se creará una tabla de Historial de registros eliminado para la tabla Productos (Products), al eliminar un  registro con Delete, se utilizará un Trigger para guardar en la tabla historial el registro eliminado.

Como crear el historia con un trigger

1. Primero crear una tabla con la misma estructura de Products

La tabla a crear es recomendable que no tenga restricciones, es posible que se elimine mas de una vez un registro y debería guardarse en la tabla, de preferencia con la fecha de eliminación.

USE Northwind
go
CREATE TABLE dbo.ProductosHistorialEliminados
(
ProductID int NOT NULL,
ProductName nvarchar(40) NOT NULL,
SupplierID int NULL,
CategoryID int NULL,
QuantityPerUnit nvarchar(20) NULL,
UnitPrice money ,
UnitsInStock smallint ,
UnitsOnOrder smallint ,
ReorderLevel smallint ,
Discontinued bit ,
FechaEliminacion Datetime
)
go

Note que se ha incluido un campo para la fecha de eliminación del tipo DateTime para guardar el momento exacto de la eliminación. Puede guardar el usuario, el equipo y algunos datos posiblemente necesarios. (Ver Triggers DDL)

2. Crear el Trigger para guardar los datos del registro eliminado

El Trigger que va a guardar los datos del registro eliminado usando Delete en la tabla Historial de Productos será como sigue

Create trigger trProductosGuardaHistorialEliminado
on Products
for Delete
As
insert into ProductosHistorialEliminados
select *, GetDate() from deleted
go

Insertaremos algunos registros en Products para luego poder eliminarlos. (Ver Insertar registros)

insert into Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
values (‘Ron Solera’,6,1,’Bot. 750ml’,70,40,0,20,0),
(‘Pavita’,3,6,’Uni. 7kg’,170,30,0,10,0),
(‘Queso Parmesano’,8,4,’Env. x 200gr’,50,20,5,10,0)
go

Los registros insertados tienen los códigos 78, 79 y 80 respectivamente.
select * from Products
go

Eliminar registros
Primero intentaremos eliminar productos que tienen registros en detalle de venta,  por ejemplo, el registro con código 10.

Delete Products where ProductID = 10
go

El mensaje es claro, no se puede eliminar porque hay un conflicto con la restricción de Clave foránea
de la tabla Order Details.
Mens. 547, Nivel 16, Estado 0, Línea 72
Instrucción DELETE en conflicto con la restricción REFERENCE “FK_Order_Details_Products”.
El conflicto ha aparecido en la base de datos “Northwind”, tabla “dbo.Order Details”, column ‘ProductID’.
Se terminó la instrucción.

3. Eliminar un producto con éxito.

Eliminar uno de los productos insertados, los que no figuran con clave foránea. Según los registros insertados líneas arriba son los que tiene código 78, 79 y 80

Delete Products where ProductID = 79
go

Listado de la tabla Historial de registros eliminados

select * from ProductosHistorialEliminados
go


Al tener el historial se puede conservar los datos del registro eliminado.

Notas importantes

  • Se sugiere evitar los campos con la propiedad Identity (Ver Identity)
  • Se recomienda para almacenar los datos numéricos el uso de Numeric. (Ver Crear tablas)
  • En ocasiones se pueden usar campos que especifiquen que los registros no se pueden eliminar debido a que son de sistema.
  • Se sugiere con por ningún motivo se implemente el borrado en cascada.