Crear un trigger que controle los INSERT, UPDATE y DELETE de una determinada tabla.
Queremos que se guarde en una tabla EmpleadosInsertados el historial de inserciones de registros realizadas en la tabla empleados, además de los datos del empleado se deberá guardar en la tabla el usuario que realizó la inserción del empleado y la fecha/hora de la operación. La primera vez el procedimiento deberá crear la tabla y rellenarla con los empleados que ya existen.
Para hacerlo aplicaremos el trigger en el FOR INSERT.
PRINT 'Empieza el ejercicio de Triggers utilizando FOR INSERT'
USE Gestion10
IF OBJECT_ID('HistorialEmpleados','TR') IS NOT NULL DROP TRIGGER HistorialEmpleados
GO
CREATE TRIGGER HistorialEmpleados
ON Empleados FOR INSERT -- se ejecutará después de insertar en Empleados
AS
BEGIN
IF OBJECT_ID('EmpleadosInsertados','U') IS NULL -- Si la tabla no existe la creamos
SELECT *,SUSER_SNAME()AS Usuario,GETDATE() AS FechaInsercion -- como es un SELECT ...INTO necesitamos usar alias de columna.
INTO EmpleadosInsertados
FROM Empleados;
ELSE
INSERT INTO EmpleadosInsertados -- Si la tabla ya existe insertamos las nuevas filas
SELECT *,SUSER_SNAME() AS Usuario,GETDATE()AS FechaInsercion
FROM Inserted;
END;
GO
USE Gestion10
BEGIN TRY --Para probar el Triger insertamos un empleado que no esté ya en empleados
INSERT INTO Empleados (numemp,nombre,ventas,cuota) VALUES (218,'El Nuevo',0,10000);
SELECT * FROM EmpleadosInsertados; -- Comprobamos que el nuevo empleado está
END TRY
BEGIN CATCH
SELECT 'error', ERROR_MESSAGE()AS Mensaje
END CATCH
GO
Queremos que no se puedan eliminar físicamente los pedidos, en vez de eliminarlo, se marcará como baja.
Para ello debemos añadir a la tabla de pedidos un campo baja que contendrá un cero o un uno, no podrá contener ningún otro valor. En un principio está a cero y cuando se intente borrar el pedido, en vez de borrar el pedido se marcará este campo a 1. Para hacerlo aplicaremos el trigger en el INSTEAD OF DELETE.
PRINT 'Empieza el ejercicio de Triggers utilizando INSTED OF DELETE'
USE Gestion10
ALTER TABLE Empleados ADD Baja BIT DEFAULT 0; -- Para crear la columna
GO
UPDATE Empleados SET Baja=0 -- Para actualizar las filas que ya están en Empleados
USE Gestion10
IF OBJECT_ID('Eliminarempleados','TR') IS NOT NULL DROP TRIGGER Eliminarempleados
GO
CREATE TRIGGER Eliminarempleados
ON Empleados INSTEAD OF DELETE -- El trigger se pondrá en marcha INSTEAD OF (En vez de) el DELETE que lanzó el usuario.
AS
BEGIN
UPDATE Empleados SET baja=1
FROM Empleados INNER JOIN Deleted ON empleados.numemp=Deleted.numemp
END;
GO
USE Gestion10
BEGIN TRY -- Comprobamos cómo funciona
SELECT 'Antes',* FROM Empleados ; -- Comprobamos los empleados que hay
DELETE Empleados WHERE numemp=210; -- Un empleado que existe
SELECT 'Después DELETE',* FROM Empleados; -- comprobamos que el 210 sigue en la tabla con el campo Baja a 1
DELETE Empleados WHERE numemp=220; -- Un empleado que no existe
SELECT 'Después DELETE',* FROM Empleados;
END TRY
BEGIN CATCH
SELECT 'error', ERROR_MESSAGE()AS Mensaje
END CATCH
GO
Una variante del ejercicio anterior sería eliminar físicamente el registro de la tabla empleados pero guardar una copia del registro eliminado en una tabla Empleados eliminados, guardando también en esa tabla la fecha de la eliminación. Para hacerlo aplicaremos el trigger en el FOR DELETE.
PRINT 'Empieza el ejercicio de Triggers utilizando FOR DELETE'
USE Gestion10
-- Como vamos a definir un nuevo trigger para la misma operación y sobre la misma tabla, primero eliminamos el primer trigger si existe.
IF OBJECT_ID('Eliminarempleados','TR') IS NOT NULL DROP TRIGGER Eliminarempleados
IF OBJECT_ID('Eliminarempleados2','TR') IS NOT NULL DROP TRIGGER Eliminarempleados2
GO
CREATE TRIGGER Eliminarempleados2
ON Empleados FOR DELETE -- Ahora dejamos que se eliminen los registros y el trigger entrará en funcionamiento después
AS
BEGIN
IF OBJECT_ID('EmpleadosEliminados','U') IS NULL
SELECT *,SUSER_SNAME()AS Usuario,GETDATE() AS FechaInsercion -- como es un SELECT ...INTO es necesario utilizar alias.
INTO EmpleadosEliminados
FROM Empleados;
ELSE
INSERT INTO EmpleadosEliminados
SELECT *,SUSER_SNAME() AS Usuario,GETDATE()AS FechaInsercion
FROM Deleted;
END;
GO
USE Gestion10
BEGIN TRY -- Comprobamos cómo funciona el trigger
SELECT 'Antes',* FROM Empleados WHERE numemp=210;
DELETE Empleados WHERE numemp=210; -- Un empleado que existe y no es padre
SELECT 'Empleados Después DELETE',* FROM Empleados WHERE numemp=210;
SELECT 'EmpleadosEliminados Después DELETE',* FROM EmpleadosEliminados;
DELETE Empleados WHERE numemp=220; -- Un empleado que no existe
SELECT 'EmpleadosEliminados Después DELETE',* FROM EmpleadosEliminados;
DELETE Empleados WHERE numemp=108; -- Un empleado que existe y es padre (salta un error de integridad referencial)
SELECT 'Empleados Después DELETE',* FROM Empleados WHERE numemp=108;
SELECT 'EmpleadosEliminados Después DELETE',* FROM Empleados WHERE numemp=108;
END TRY
BEGIN CATCH
PRINT 'Un error: ' + ERROR_MESSAGE()
END CATCH
GO
Hacer que se actualicen automáticamente las existencias de los productos cuando se inserte un nuevo pedido o cuando se rectifique la cantidad de uno existente. Se supone que un pedido produce una reducción del stock (existencias) del producto. Para hacerlo aplicaremos el trigger en el FOR INSERT, UPDATE, y DELETE.
PRINT 'Empieza el ejercicio de Triggers utilizando FOR INSERT, UPDATE, DELETE'
USE Gestion10
IF OBJECT_ID('ActualizaStock','TR') IS NOT NULL DROP TRIGGER ActualizaStock
GO
CREATE TRIGGER ActualizaStock
ON Pedidos FOR INSERT,UPDATE,DELETE -- Definimos el trigger para todas las operaciones, luego actuaremos según la operación que lo ha desencadenado.
AS
BEGIN
UPDATE Productos SET existencias=existencias + Deleted.cant
FROM Productos INNER JOIN Deleted ON idfab=fab AND idproducto=producto
-- Sumamos la cantidad de los pedidos que se han borrado al stock del producto
UPDATE Productos SET existencias=existencias - Inserted.cant
FROM Productos INNER JOIN Inserted ON idfab=fab AND idproducto=producto
-- Restamos la cantidad de los pedidos que se han insertado al stock del producto.
-- Cuando la operación sea un UPDATE primero se elimina el que había y se inserta el nuevo,
-- luego tendremos una fila en deleted y una fila en inserted y el stock finalmente quedará correcto.
END;
GO
USE Gestion10
BEGIN TRY -- Lo probamos:
SELECT 'Antes',* FROM productos WHERE idfab='bic' AND idproducto=41003; -- Para comprobar las existencias del producto
INSERT INTO Pedidos (numpedido,fechapedido,rep,fab,producto,clie,cant,importe)
VALUES (010101113,GETDATE(),108,'bic',41003,2103,10,1000);
-- Insertamos un pedido de 10 unidades del producto en cuestion.
SELECT 'Después INSERT',* FROM productos WHERE idfab='bic' AND idproducto=41003;
-- Comprobamos que el campo existencia se ha reducido en 10 unidades
UPDATE Pedidos SET cant= 5 WHERE numpedido=010101113;
/* Ahora modificamos la cantidad en un pedido del mismo producto,
el pedido antes tenía 10 y ahora 5 unidades,
como se ha reducido en 5 unidades el stock debe de aumentar en esa cantidad.*/
SELECT 'Después UPDATE',* FROM productos WHERE idfab='bic' AND idproducto=41003;
-- Comprobamos que el campo existencias ha subido 5 unidades
DELETE Pedidos WHERE numpedido=010101113;
-- Eliminamos el pedido, con lo que el stock debe de aunmentar 5 unidades
SELECT 'Después DELETE',* FROM productos WHERE idfab='bic' AND idproducto=41003;
-- Comprobamos que es así
END TRY
BEGIN CATCH
SELECT 'error', ERROR_MESSAGE()AS Mensaje
END CATCH
GO
Aviso legal: Este curso es gratuito siempre que se visualice desde la página web de aulaClic. No está permitido utilizar estos cursos en empresas, academias o centros de enseñanza privados.
© aulaClic S.L. Todos los derechos reservados.
Prohibida la reproducción por cualquier medio.
Febrero-2010.