Unidad 9. Ejercicio paso a paso: Triggers


Objetivo

Crear un trigger que controle los INSERT, UPDATE y DELETE de una determinada tabla.

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') 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

   Inicio    






.