Triggers Historial de cambios en una tabla

Triggers Insert y Update – Creando un Historial de cambios

Los triggers DML son procedimientos guardados en la base de datos que se disparan cuando se insertan registros, cuando se actualizan los datos de un registro o cuando se eliminan registros.
Este ejercicio muestra como crear un Historial de cambios usando un Trigger, el trigger se disparará cuando se inserte o actualice un registro.
Para mayor información Ver Triggers

Usando la base de datos Norhtwind
use Northwind
go

Ejercicio 1

Historial de cambios en la tabla Shippers.

La tabla Shippers (Compañías de envío) tiene los campos: ShipperID, Companyname y Phono. Primero se creará una tabla HistorialShippers con los campos Código, Nombre, Teléfono y Fecha.
Create table HistorialShippers
(
HistorialShippersCodigo nchar(10),
HistorialShippersNombre nvarchar(40),
HistorialShippersFono nvarchar(24),
HistorialShippersFecha Date
)
go
Crear el trigger para la tabla Shippers que se dispare cuando se inserta o modifica un registro
Create trigger triggerShippersHistorial on Shippers
for insert, update
As
Begin
Insert into HistorialShippers
select inserted.ShipperID, inserted.CompanyName,
inserted.Phone, GetDate()
from inserted
End
go

Antes de insertar registros, visualizar los registros en las tablas.
En Shippers
select * from Shippers
go

Registros de la tabla Shippers.

En HistorialShippers
select * from HistorialShippers
go
La instrucción muestra que no hay registros.

Insertar un registro en la tabla Shippers, esto hará que se dispare el Trigger y se inserte un registro en la tabla creada HistorialShippers
insert into Shippers (CompanyName, Phone)
values (‘Trainer SQL’,’87852541′)
go
Prueba con la actualización de los datos de un registro.
El registro insertado líneas arriba se generó su código 4, se cambiará su nombre y teléfono.
update Shippers set CompanyName = ‘Capacitador SQL’,
Phone = ‘963258741’ where ShipperID = 4
go
Probando con insertar otro registro
insert into Shippers (CompanyName, Phone)
values (‘Tracks Moves’,’952369985′)
go
Puede observar que la tabla HistorialShippers tiene los datos de los registros que se insertaron o que se actualizaron.
select * from HistorialShippers
go

Ejercicio 2

Se va a crear una tabla Ciudades, el historial de cambios en esta tabla va a incluir la operación realizada, si es inserción y al realizar una actualización se va a guardar el original, el cambio y el nombre del usuario que realizó el cambio.
Create table Ciudades
(
CiudadesCodigo nchar(4),
CiudadesNombre nvarchar(100),
constraint CiudadesPK Primary key (CiudadesCodigo)
)
go
Tabla para el historial de cambios en Ciudades, se va a incluir la operación que puede ser inserción o modificación, la fecha y el usuario que la realizó.
Create table HistorialCiudades
(
HistorialCiudadesCodigo nchar(4),
HistorialCiudadesNombre nvarchar(100),
HistorialCiudadesOperacion nvarchar(15),
HistorialFecha DateTime,
HistorialEstado nvarchar(10),
HistorialUsuario nvarchar(128)
)
go
Trigger para insertar en la tabla Ciudades
Create trigger triggerCiudadesInsertar on Ciudades for insert
As Begin
insert into HistorialCiudades
select inserted.CiudadesCodigo, inserted.CiudadesNombre,
‘inserción’, Getdate(), ‘insertado’, SYSTEM_USER
from inserted
End
go
Trigger para actualización en la tabla Ciudades
Create trigger triggerCiudadesActualizacion on Ciudades for update
As Begin
insert into HistorialCiudades
select deleted.CiudadesCodigo, deleted.CiudadesNombre,
‘modificacion’, Getdate(), ‘original’, SYSTEM_USER
from deleted
insert into HistorialCiudades
select inserted.CiudadesCodigo, inserted.CiudadesNombre,
‘modificacion’, Getdate(), ‘cambiado’,SYSTEM_USER
from inserted
End
go
Ver la tabla historial
select * from HistorialCiudades
go
El resultado muestra que no existen registros

Insertar registros en la tabla Ciudades
insert into ciudades values (‘2541′,’Trujillo’)
go
Ver los datos
select * from Ciudades
select * from HistorialCiudades
go

Registros de las tablas Ciudades y de Historial de ciudades.

Insertar más ciudades
insert into ciudades values (‘8521′,’Piura’)
go
insert into ciudades values (‘7458′,’Huaraz’)
go
insert into ciudades values (‘4169′,’Lima’)
go
Modificar Piura
update Ciudades set CiudadesNombre = ‘Piura calor’ where CiudadesCodigo = ‘8521’
go
Ver los datos
select * from Ciudades
select * from HistorialCiudades
go