Triggers Historial de registros eliminados

Trigger historial de registros 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.

Creando 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.

Pasos para crear el historial

  1. Primero crear una tabla con la estructura de los datos que se desean registrar de los registros eliminados, para el ejercicio será la tabla Products a la que se agregará la fecha de eliminación.
    La tabla a crear es recomendable que no tenga restricciones y que se cree en un esquema adecuado, 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
  2. Crear 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.
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.
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.

select * from ProductosHistorialEliminados
go
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)