Mysql Triggers

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

• La orden sql que crea un trigger es CREATE TRIGGER.

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

• Creando triggers desde MysqlWorkBench y phpmyadmin
• 

  Desde el MysqlWorkBench podemos ver los triggers creados y asociados a cada una de las tablas, pero no se pueden crear desde aquí.

• 

  Debemos ir a la opción de cambiar la estructura de la tabla (botón derecho sobre la tabla sobre la que vamos a querer crear un trigger y escoger Alter Table).

• 

  En la parte baja debemos de presionar sobre la pestaña Triggers. Una vez presionada ya podemos ver, crear y eliminar triggers de cada tipo. Sólo tenemos que pasar el ratón por encima del tipo correspondiente y aparecerán unos botones para poder crear (+) o eliminar (-) un trigger.

• 

  En PhpMyAdmin debemos de seleccionar una base de datos y escoger la opción superior que indica Disparadores.

• 

  Aparece la lista de triggers creados junto a la tabla asociada y el tipo de trigger. Podremos crear, borrar o modificar uno existente.

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 %).
• 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 [: Otra orden SQL que se puede emplear es 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

• La orden SQL para borrar un trigger es DROP TRIGGER.

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.

Ejercicios propuestos

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