TRIGGERS - (DML - DDL)

Amigos, 

    Quiero acercarles un artículo que he preparado para este blog relacionado con los triggers.     Tipos, utilidad, consideraciones generales....

     Todo al estilo de este blog, sencillo y con ejemplos.

     Espero que les sea útil, saludos.

DML Triggers

·         Son procedimientos almacenados  que se ejecutan automáticamente ante un evento DML  (Update – Delete – Insert) que afecta  una tabla o vista.

·         Se los puede utilizar para, una vez desencadenados:

-          Forzar reglas de negocio

-          Mantener la integridad de datos

-          Querear otras tablas

-          Ejecutar complejas instrucciones SQL

·         La integridad debiera ser forzada al nivel más bajo por índices que formen parte de un Primary Key o Foreing Key contraints -

·         Los CHECK Constraints son otra alternativa

·         Para asegurar la Integridad Referencial nada mejor que los Foreing Key

·         Los DML TRIGGERS son especialmente utilizados cuando,con el simple uso de un constraint, no se cubren  las necesidades de una aplicación

     

·         Consideraciones Generales y Beneficios:

·        *   No se pueden invocar por si mismos, se disparan automáticamente

·        *   No reciben ni retornan parámetros

·        *    A diferencia de los constraint “check” pueden hacer referencia a otras tablas (por ejemplo se puede controlar una inserción en una tabla de ventas si y solo si el valor de un campo stock de una tabla artículos sea mayor a x cantidad)

·        *   Se pueden crear más de un trigger para un mismo evento en una tabla, con lo cual se pueden controlar múltiples alternativas sobre la misma tabla.

·       *   Permiten evaluar el estado de una tabla antes y después de una modificación y tomar acciones acorde a la evaluación

·         *   Permiten customizar mensajes de error, algo que los constraints en general no permiten.

·         Syntaxis:

CREATE TRIGGER <Nombre del Trigger>

ON <Nombre de la Tabla>

AFTER <INSERT,DELETE,UPDATE>

      AS

BEGIN

SET NOCOUNT ON;

-- Inserta aquí las instrucciones

END

·         Consideraciones Adicionales:

-          Los triggers DML utilizan dos tablas especiales denominadas Inserted y Deleted.

-          Son tablas creadas automáticamente por el SQL con la misma estructura que la tabla sobre la cual está definido el trigger

-          La tabla Inserted solo tiene datos en operaciones de Insert y Update

-          La tabla Deleted  sólo tiene datos en operaciones de Delete y Update

-          En caso de un update las tablas Inserted y Deleted tienen data al mismo tiempo.

-          No se pueden modificar los datos de estas tablas

·         Ejemplos:

1)      Se graba un histórico de stock cada vez que se modifica un artículo de la tabla “articulos”

      -----------------------------------------------------------------

      --  TRIGGER DML                                                                            

      --  Detalle: este trigger genera un histórico de stock cada vez 

          que se modifica la existencia de un artículo --

      -----------------------------------------------------------------

      CREATE TRIGGER TR_ARTICULOS

      ON ARTICULOS

      AFTER UPDATE

      AS

        BEGIN

         INSERT INTO HCO_STOCK

         (IDARTICULO, STOCK, FECHA)

         SELECT IDARTICULO, STOCK, getdate()

         FROM INSERTED

        END

       --- Con este evento UPDATE se desencadena el Trigger

      UPDATE ARTICULOS

      SET STOCK = STOCK + 10

      WHERE IDARTICULO = 1

2)       Podemos hacer que el trigger del ejemplo 1 se desencadene sólo si una columna es afectada

      CREATE TRIGGER TR_ARTICULOS

      ON ARTICULOS

      AFTER UPDATE

      AS

        BEGIN

           IF UPDATE (STOCK)    -- sólo si actualiza STOCK

            BEGIN

             INSERT INTO HCO_STOCK

             (IDARTICULO, STOCK, FECHA)

             SELECT IDARTICULO, STOCK, getdate()

             FROM INSERTED

            END

END

3)      Podemos hacer que el trigger  deshaga toda la operación incluyendo un ROLLBACK

      CREATE TRIGGER TR_ARTICULOS

      ON ARTICULOS

      AFTER UPDATE

      AS

        BEGIN

             INSERT INTO HCO_ARTICULOS

             (IDARTICULO, STOCK, FECHA)

             SELECT IDARTOCULO, STOCK, getdate()

             FROM INSERTED

               

             ROLLBACK

END

4)      Podemos DESACTIVAR/ACTIVAR  un Trigger o Todos lo Triggers de la tabla

      ----------------------------------------------------------

      -- Desactivar y Activar Todos los Triggers de una Tabla --

      ---------------------------------------------------------

      -- Desactiva todos los trigger de la tabla ARTICULOS

      ALTER TABLE ARTICULOS DISABLE TRIGGER ALL

      GO

      -- Activa todos los trigger de la tabla ARTICULOS

      ALTER TABLE ARTICULOS ENABLE TRIGGER ALL

      ----------------------------------------------------

      -- Desactivar y Activar Un Trigger en Particular --

      ---------------------------------------------------

      -- Desactiva el trigger TR_STOCK

      DISABLE TRIGGER TR_STOCK ON ARTICULOS

      GO

      -- Activa el trigger TR_STOCK

      ENABLE TRIGGER TR_STOCK ON ARTICULOS

      GO  

DDL Triggers

·         Son triggers especiales que se crean a nivel de base de datos y que disparan en respuesta a eventos DML  (Update – Delete – Insert)

·         Suelen ser utilizados para ejecutar tareas administrativas en una base de datos auditando y regulando cierta clase de eventos.

·         Syntaxis:

CREATE TRIGGER <Nombre del Trigger>

ON DATABASE

FOR <DROP TABLE, ALTER TABLE>

AS

BEGIN

   SET NOCOUNT ON;

   -- Inserta aquí las instrucciones

END

      

·         Ejemplos:

1)      El siguiente trigger impide que sentencias DROP TABLE y ALTER TABLE a nivel de Base de Datos

               CREATE TRIGGER TR_Seguridad

       ON DATABASE FOR DROP_TABLE, ALTER_TABLE

       AS

       BEGIN

         RAISERROR ('No está permitido borrar ni modificar tablas !' , 16, 1)

         ROLLBACK TRANSACTION

       END