miércoles, 6 de agosto de 2014

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 InsertedDeleted.
-          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

19 comentarios:

  1. Buenos días, si utilizo DROP TRIGGER TR_seguridad , elimimo la acción que ejecuté o hay otra manera de realizarlo.

    ResponderEliminar
  2. Buen aporte Gustavo Herrera, en la Universidad estoy viendo Trigger y la verdad que con los ejemplos dados pude entenderlo mejor.

    Saludos desde Lima,Peru.

    ResponderEliminar
  3. Muchisimas gracias hermano, la verdad muy sencillas y fáciles de entender...

    ResponderEliminar
  4. buena lectura tratando de utilizar para retener lo aprendido

    ResponderEliminar
  5. Estimado, se puede ejecutar instrucciones a partir de un tipo de valor insertado, es decir si en la tabla artículos el valor de un campo es mayor a 10 por ejmplo

    ResponderEliminar
  6. Muy buen aporte y fácil de entender.

    ResponderEliminar
  7. Hola muy interesante la info, el uso de los TRIGGERS puede ser una opción en el momento de crear una tabla HISTORIAL donde se registren los cambios que los usuarios hagan??

    ResponderEliminar
  8. Excelente info. Los Triggers están disponibles para la versión STANDAR de SQL Server?

    ResponderEliminar
  9. Hola muy bueno el articulo y los ejemplos. Sabes que soy nueva en esto y no se como hacer un tigger que solo me cambie un dato en el registro que está insertando . Escribi esto pero al revisar la tabla al ver que se demoraba me percate que me cambio ese campo de todos los registros ALTER TRIGGER [dbo].[AELECTRONICA]

    ON [dbo].[DOCU_DB] FOR INSERT

    AS

    UPDATE DOCU_DB SET ESELECTR=1

    se agradece la ayuda

    ResponderEliminar
    Respuestas
    1. creo que es por que no le has especificado que campo quieres actualizar usando la expresión WHERE

      Eliminar
  10. Muy bien explicado, pero el Rollback no me ha funcionado y he tenido que poner un ; antes del END.

    ResponderEliminar
  11. Me encanto tu explicación, gracias por compartir , solo tengo una duda, por lo que pones entiendo que no puedes ELIMIAR un trigger solo desactivar, esto es correcto?

    ResponderEliminar
  12. Hola, alguien utilizó un a tigger para leer mis mensajes en facebook.Hay alguna manera de bloquear esta función¿

    ResponderEliminar
  13. Este comentario ha sido eliminado por un administrador del blog.

    ResponderEliminar
  14. Gracias por compartir esa informacion!!

    ResponderEliminar