Mysql Triggers

De MediaWiki
Saltar a: navegación, buscar

Introducción

  • Un trigger viene a ser como un procedimiento almacenado que se ejecuta automáticamente cuando sobre una tabla se realiza alguna operación que implique modificar sus datos (DELETE, INSERT, UPDATE).
Por lo tanto un trigger va a estar 'asociado' a una tabla y a un 'tipo de operación' sobre la tabla.


  • Los usos que le podemos dar a un trigger son:
  • Monotorizar y registrar operaciones sobre las tablas (podemos guardar información sobre quien y cuando se realizó alguna modificación).
  • Verificar que los datos sean correctos antes de añadirlos o usarlos para una modificación.
  • Un mecanismo para implementar columnas calculadas (por ejemplo, la tabla Libros tiene una columna 'numero de ejemplares' y disponemos de la tabla Ejemplares, donde guardamos cada ejemplar. Cada vez que demos de alta un nuevo ejemplo, tendremos que actualizar la columna de Libros).
  • Copias de seguridad de los datos antes de que sean modificados o borrados.


  • Debemos de tener en cuenta que un trigger no se va a ejecutar en caso de una operación en cascada debido a las restricciones de integridad referencial (clave primaria-clave foránea).



Crear Triggers

Disponéis de varios ejemplos en este enlace.


  • Es necesario tener el permiso TRIGGER otorgado para poder crear un trigger.
Dicho permiso se encuentra en el nivel de seguridad de tablas, por lo que es necesario indicar sobre qué tabla va a tener permiso para crear el trigger.


  • Básicamente cuando decidimos crear un trigger necesitamos determinar:
  • Sobre qué tabla va a aplicarse el trigger.
  • Sobre qué operación se va a aplicar (INSERT / DELETE / UPDATE)
  • Queremos que el trigger se ejecute antes o después de que se realice la operación sobre la tabla (BEFORE => antes; AFTER => después).


  • Indicar que si la operación SQL que va a provocar la ejecución del trigger afecta a múltiples filas (por ejemplo un borrado de muchas filas), el trigger se ejecutará una vez por cada fila afectada.


  • Dentro del trigger vamos a poder acceder a los valores de la fila de la tabla anterior y posterior a la ejecución de la orden sql. Es decir, si realizo una operación de UPDATE sobre una columna, dicha columna tendrá un valor antes de la ejecución de la orden SQL y otro después de la ejecución, ya que le estoy enviando un nuevo valor. Dentro del trigger puedo acceder a dos 'alias' de la tabla a la que afecta el trigger con las mismas columnas que la tabla original:
  • NEW: Tabla que posee los datos de cada columna con los nuevos valores.
  • OLD: Tabla que posee los datos de cada columna con los valores antiguos.
Por ejemplo:
TABLA ALUMNOS:
---nif---- ---nombre----
---1a----- ---Angel-----
Realiza una operación UPDATE con la orden: UPDATE ALUMNOS SET nombre = 'Pedro' WHERE nif='1a'
Si tengo asociado un trigger UPDATE y que se ejecute antes de la orden SQL (tipo BEFORE), dentro del código del trigger:
  • NEW.nombre tendrá el valor 'Pedro'.
  • OLD.nombre tendrá el valor 'Angel'.
Operaciones
  • UPDATE:
  • NEW: Valores nuevos
  • OLD: Valores antiguos
  • INSERT
  • NEW: Valores nuevos
  • OLD: NO EXISTE
  • DELETE
  • NEW: NO EXISTE
  • OLD: Valores antiguos


  • La orden LOAD DATA también provoca la ejecución del trigger ya que realiza operaciones de INSERT sobre las tablas.


  • Es posible tener más de un trigger sobre la misma tabla y con el mismo evento.
En estos casos, la ejecución se hará uno después de otro en el orden en que fueron creados.
Si queremos modificar dicho orden, a la hora de crear el trigger tendremos que utilizar la opción trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
De tal forma que el trigger que se está creando se ejecute antes (PRECEDES) o después (FOLLOWS) que el trigger que ya existe (other_trigger_name)


  • Cuando se define un trigger, este estás asociado al usuario que lo creó (CREATE DEFINER=`root`@`localhost` TRIGGER .....)
Dicho usuario debe tener el permiso TRIGGER otorgado con grant sobre la tabla, lo que le da derecho a crear, borrar, mostrar y ejecutar un trigger.
Cuando otro usuario realiza una operación SQL sobre una tabla en la que está asociado el trigger, el usuario que creó el trigger debe conservar el permiso TRIGGER para que se pueda ejecutar.


  • Dentro del cuerpo del trigger vamos a poder acceder a los valores antiguos y nuevos como hemos visto.
  • Para poder modificar el valor nuevo (SET NEW.col=valor) el usuario que creó el trigger necesita tener permiso UPDATE sobre la tabla.
  • Para poder consultar el valor nuevo o antiguo (OLD.col o NEW.col) el usuario que creó el trigger necesita tener el permiso SELECT sobre la tabla.


Gráficamente




Visualizar triggers

  • La orden SQL para ver los triggers asociados a una base de datos o a una tabla es SHOW TRIGGERS.
  • Por defecto muestra los triggers de la base de datos activa. Si queremos mostrar los de otra debemos de utilizar la opción FROM nombre_BD.
  • Si queremos buscar por los triggers asociados a una determinada tabla podemos hacer uso de la cláusula LIKE que busca por patrón (símbolo comodín el %) nombres de tablas.
  • Si queremos buscar dentro de los resultados aquellas filas que cumplan alguna condición asociada al valor de alguna columna debemos hacer uso la cláusula WHERE. Por ejemplo: show triggers where Definer like 'root%'


  • Si queremos obtener información sobre un trigger específico por su nombre, debemos hacer uso de la orden SQL SHOW CREATE TRIGGER.


  • Recordar que la base de datos y tabla donde se guarda toda la información de los triggers es: information_schema.triggers




Modificar triggers

  • La modificación de triggers no es posible.
Es necesario borrar y volver a crear el trigger.




Borrar triggers



Aspectos varios

  • Como comentamos al principio, uno de los objetivos de utilizar triggers era el de registrar las operaciones sobre las tablas.
Uno de los datos que puede que necesitemos es el de obtener el nombre del usuario que realiza la operación.
Para obtener podemos hacer uso de la función USER().
Cuidado con utilizar la función CURRENT_USER() ya que esta devuelve el usuario que creó el trigger


  • Funciones interesantes:
  • NOW(): obtiene el DATETIME actual
  • SYSDATE(): obtiene el DATETIME actual
  • CURDATE: Obtiene la fecha actual.
  • DATE(SYSDATE()): obtiene el DATE actual
  • TIME(SYSDATE()): obtiene el TIME actual
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'mensaje a mostrar'
Cancela la operación que se estaba realizando y muestra un mensaje.
Lleva el valor 45000 ya que este valor significa 'excepción definida por el usuario' (“unhandled user-defined exception”)



Ejercicios propuestos

  • Haz que no se pueda añadir un nuevo departamento si el número de caracteres del nombre es inferior a 5 caracteres.


  • Cada vez que se añada un nuevo departamento, asigna como 'manager general' al empleado que lleva más tiempo ya como manager.


  • Crea una tabla de nombre REGISTRO con las columnas:
  • id autonumérica Clave primaria
  • usuario: varchar(100)
  • tabla: varchar(100)
  • operacion: varchar(10)
  • fecha-hora: datetime
Haz que se registren las operaciones de alta, baja y modificación sobre la tabla 'departments'.
Si ya existen triggers creados, nos los modifiques, crea uno nuevo.


  • Impide que se pueda añadir o modificar un salario a un empleado si este es inferior a 30000 euros o superior a 300000 euros anuales.


  • Crea una tabla de nombre CONTADOR con las columnas:
  • id autonumérica Clave primaria
  • tipo: varchar(100)
  • valor: int
Añade dos filas con los valores para tipo/valor: 'numEmpleados'/0 'numDepartamentos'/0
Haz que cada vez que haya alguna operación que modifique el número de empleados o de departamentos, se actualice el número total de los mismos.


  • Ejecuta la orden SQL que muestre los triggers creados en la tabla 'departments'.


  • Ejecuta la orden SQL que muestre la información del primer trigger creado.



-- Ángel D. Fernández González -- (2017).