Ejercicio paso a paso 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') HistorialEmpleados
IS
NOT
NULL
DROP
TRIGGER
GO CREATE TRIGGER
HistorialEmpleados
ON Empleados FOR
INSERT
-- se ejecutará después de insertar en Empleados
AS BEGIN IF OBJECT_ID('EmpleadosInser tados','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 insertamos las nuevas filas
INTO
EmpleadosInsertados
--
Si
la
tabla
ya
existe
SELECT *,SUSER_SNAME() AS Usuario,GETDATE()AS FechaInsercion FROM
Inserted;
END; GO USE Gestion10 BEGIN TRY empleados INSERT Nuevo',0,10000);
--Para probar el INTO
Empleados
Triger insertamos un empleado que no esté ya en (numemp,nombre,ventas,cuota) (numemp,nombre,ven tas,cuota)
SELECT * FROM EmpleadosInsertados;
-- Comprobamos que
END TRY BEGIN CATCH SELECT 'error', ERROR_MESSAGE()AS Mensaje END CATCH GO
VALUES
(218,'El
el nuevo empleado está
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=Delete d.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() como es un SELECT ...INTO es necesario utilizar alias.
AS
FechaInsercion
--
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 SELECT 'EmpleadosEliminados DELETE Empleados WHERE
DELETE',* FROM
Empleados WHERE numemp=210;
Después DELETE',* FROM EmpleadosEliminados;
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 SELECT 'EmpleadosEliminados
DELETE',* FROM
Empleados WHERE numemp=108;
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 operaciones, luego actuaremos según la operación que lo ha desencadenado.
todas
las
AS BEGIN UPDATE
Productos SET existencias=existencias +
FROM idproducto=producto
Productos
INNER
JOIN
Deleted.cant
Deleted
-- Sumamos la cantidad de los pedidos
ON
idfab=fab
AND
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 se inserta el nuevo,
UPDATE primero se elimina el que había y
-- 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 SELECT 'Después idproducto=41003;
INSERT',*
-- Comprobamos que el campo UPDATE Pedidos SET
FROM
productos
cuestion. WHERE
idfab='bic'
AND
existencia se ha reducido en 10 unidades
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
productos
WHERE
aumentar
en esa
cantidad.*/ SELECT 'Después idproducto=41003;
UPDATE',*
-- Comprobamos que el campo DELETE Pedidos WHERE
FROM
AND
existencias ha subido 5 unidades
numpedido=010101113;
-- Eliminamos el pedido, con lo que el stock debe de SELECT 'Después idproducto=41003;
idfab='bic'
DELETE',*
-- Comprobamos que es así
FROM
productos
aunmentar 5 unidades WHERE
idfab='bic'
AND
END TRY BEGIN CATCH SELECT 'error', ERROR_MESSAGE()AS Mensaje END CATCH GO
http://www.lawebdelprogramador.com/foros/SQL/1221953-Trigger_+_AFTER_UPDATE.html#i1222109
Cuando se INSERTe un pedido, entrará en funcionamiento el trigger ActualizaVentasEmpleado y se ejecutarán las instrucciones que aparecen después de AS, en este caso actualizará (UPDATE) la tabla empleados sumará a las ventas del empleado (ventas) el importe del pedido insertado (inserted.importe), y sólo actualizará el empleado cuyo numemp coincida con el campo rep del pedido insertado (WHERE numemp=inserted.rep). -- Ahora comprobamos que funciona SELECT * FROM empleados WHERE
numemp=108;
INSERT INTO pedidos (numpedido,fechapedido,rep,clie,cant,importe,fab,producto) VALUES
(123456789,getdate(),108,2103,10,100,'Aci',41001)
SELECT * FROM empleados WHERE
numemp=108;
Vemos que al insertar un pedido de 100 € del empleado 108, sus ventas han aumentado en 100€. ALTER TRIGGER Permite modificar la definición del desencadenador, no permite cambiar su nombre, para cambiar el nombre de un desencadenador hay que eliminarlo (DROP TRIGGER) y volver a crearlo (CREATE TRIGGER). ALTER TRIGGER [NombreEsquema.]NombreTrigger ON {tabla|vista} {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} [WITH APPEND] AS sentencia_sql
[;] [,...n ]
La sintaxis es similar a la instrucción CREATE TRIGGER. Ejemplo: ALTER TRIGGER
ActualizaVentasEmpleados
ON pedidos FOR INSERT AS UPDATE FROM
empleados SET ventas=ventas+inserted.impor te empleados, inserted
WHERE numemp=inserted.rep AND
inserted.importe IS NOT NULL;
Hemos modificado el desencadenador para que si el importe del pedido es nulo, no haga nada, no actualice con un valor nulo. Realiza el siguiente Ejercicio Triggers para practicar la creación de desencadenadores.
9.13. DISABLE TRIGGER En ocasiones puede ser útil inhabilitar temporalmente un desencadenador sin que por ello suponga eliminarlo, para estos casos podemos utilizar la sentencia DISABLE TRIGGER.
DISABLE TRIGGER
{[NombreEsquema.]NombreTrigg er [,...n] | ALL }
ON {NombreTablaVista | DATABASE |
ALL SERVER} [;]
Ejemplo: DISABLE TRIGGER
ActualizaVentasEmpleado ON pedidos;
Deshabilita el desencadenador que hemos creado anteriormente, si después de ejecutar esta sentencia se introduce un nuevo pedido, el empleado correspondiente no se actualizará. Lo podemos comprobar con: SELECT * FROM empleados WHERE INSERT INTO pedidos
numemp=108;
(numpedido,fechapedido,rep ,clie,cant,importe,fab,produ cto) VALUES
(123456791,getdate(),108,2103,10,300,'Aci',41001)
SELECT * FROM empleados WHERE
numemp=108;
DISABLE TRIGGER ALL ON pedidos;
Deshabilita todos los desencadenadores asociados a la tabla pedidos. DISABLE TRIGGER ALL ON DATABASE;
Deshabilita todos los desencadenadores definidos en la base de datos actual. DISABLE TRIGGER ALL ON ALL SERVER;
Deshabilita todos los desencadenadores definidos en el servidor. Pág. 9.9
CREATE TRIGGER UpdateLinhasDoc ON LinhasDoc AFTER UPDATE AS DECLARE @IdCabecDoc Uniqueidentifier DECLARE @NumLinha SMALLINT DECLARE @PrecUnit FLOAT DECLARE @Quantidade SMALLINT DECLARE @QuantidadeDel SMALLINT DECLARE @QuantidadeOrig SMALLINT DECLARE @Status CHAR(3) SET NOCOUNT ON IF UPDATE(PrecUnit) or UPDATE(Quantidade) BEGIN SELECT @PrecUnit = PrecUnit, @Quantidade = Quantidade, @IdCabecDoc = IdCabecDoc, @NumLinha = NumLinha FROM inserted SELECT @QuantidadeDel = Quantidade FROM deleted IF EXISTS (SELECT * FROM MiddleLinhasDoc m JOIN inserted i ON m.IdCabecDoc = i.IdCabecDoc and m.NumLinha = i.NumLinha) BEGIN SELECT @Status = m.StatusLinha, @QuantidadeOrig = m.Quantidade FROM MiddleLinhasDoc m JOIN inserted i ON m.IdCabecDoc = i.IdCabecDoc and m.NumLinha = i.NumLinha IF (@Status LIKE 'INS') BEGIN UPDATE MiddleLinhasDoc SET PrecUnit = @PrecUnit, Quantidade = @Quantidade WHERE IdCabecDoc = @IdCabecDoc and NumLinha = @NumLinha END IF (@Status LIKE 'UPD') BEGIN BEGIN UPDATE MiddleLinhasDoc SET PrecUnit = @PrecUnit, Quantidade = (@Quantidade - @QuantidadeDel) + @QuantidadeOrig WHERE IdCabecDoc = @IdCabecDoc and NumLinha = @NumLinha END
END END ELSE BEGIN SELECT @PrecUnit = PrecUnit, @Quantidade = Quantidade, @IdCabecDoc = IdCabecDoc, @NumLinha = NumLinha FROM inserted INSERT INTO MiddleLinhasDoc(IdCabecDoc, NumLinha, PrecUnit, Quantidade, StatusLinha) VALUES (@IdCabecDoc, @NumLinha, @PrecUnit, @Quantidade - @QuantidadeDel, 'UPD') END END
