Programación de disparadores en MySQL
Los disparadores o llamados comúnmente en programación “triggers” son un tipo de objetos que se encuentran en las bases de datos. Al realizarse un determinado evento u operación en dicha base de datos nuestro trigger o disparador ejecuta automáticamente una acción para la que previamente le hemos programado.
Los triggers se accionan automáticamente al realizarse las operaciones de INSERT, DELETE o UPDATE que son las de inserción, borrado o actualización respectivamente. Al ejecutarse alguna de dichas acciones nuestros disparadores realizan una instrucción o bloque de instrucciones para las que previamente han sido programados.
La estructura de un disparador en MySQL es la siguiente:
[ DEFINER = { usuario |CURRENT_USER } ]Esta parte del código nos indica al gestor de bases de datos qué usuario tiene privilegios para poder llamar a nuestros disparadores cuando vayan a surgir los eventos DML. El valor por defecto es el de CURRENT_USER.
Para elegir el nombre de nuestro trigger hay un criterio muy bueno a seguir que es: Primero poner el nombre de la tabla, después la inicial de la operación DML, (I de Insert, D de Delete o U de Update) y después la inicial del momento de la ejecución (ya sea A de after o B de before).
BEFORE | AFTERÚnicamente indica el momento en que se ejecutará nuestro disparador, es decir si el trigger se dispara antes del evento DML (before) o después (after).
INSERT | DELETE | UPDATENos indica cual será la sentencia que usaremos para que se dispare nuestro trigger, Insert para introducir datos, Delete para borrarlos o Update para modificarlos.
ON nombre de la tablaNos indica la tabla asociada a nuestro disparador.
FOR EACH ROWSolamente nos indica que el disparador se va a ejecutar por cada fila de nuestra tabla definida anteriormente en el ON.
La sentencia de sql final o el bloque de instrucciones será la o las sentencias que nuestro disparador ejecutará al entrar en acción.
Ejemplo práctico de trigger
Ahora veremos un ejemplo práctico de cómo programar un trigger para vender billetes de autobús para un determinado trayecto mientras queden asientos libres.
Podremos utilizar cualquier software que acepte triggers y procesos almacenados.
1º Creamos una base de datos:
2º Seguido creamos dos tablas en nuestra base de datos. En una de ellas es en la que almacenaremos los billetes vendidos y la otra se crea para que podamos saber cuántos billetes quedan disponibles para el viaje determinado.
Aquí la tabla que contendrá la información relativa al número de billetes vendidos:
Seguido creamos la tabla donde guardamos la información sobre el número de billetes disponibles para cada viaje en concreto:
3º Ahora creamos la tabla para almacenar los diferentes viajes:
4ª Después de crear la base de datos y las tablas vamos a crear la consulta de SQL del trigger, pero antes de esto debemos saber qué son los identificadores NEW y OLD en los triggers.
Para que nuestro disparador se relacione con una o varias columnas en concreto de la tabla tenemos que utilizar los identificadores mencionados anteriormente NEW Y OLD.
OLD: indica el valor antiguo de la columna
NEW: el valor nuevo que pudiese tomar.
Como ejemplo: OLD.idviaje o NEW.idviaje.
Con la sentencia INSERT sólo podremos utilizar el identificador NEW pues con INSERT la función que se genera es la de introducir nuevos valores a la o las columnas.
Si utilizamos la sentencia DELETE en este caso debemos utilizar el identificador OLD pues con DELETE estamos borrando valores que ya existían con anterioridad.
En cambio si usamos la sentencia UPDATE podemos utilizar OLD y NEW, ya que nos podemos referir a valores antiguos o nuevos pues modificaremos registros que ya existían por otros valores.
Ahora que entendemos los identificadores la sentencia sql quedaría así:
Si ejecutásemos la sentencia sql en un programa que lo soportase veríamos que efectivamente se ha creado el trigger.
Para poder ver el funcionamiento introducimos unos valores lógicos para un viaje de autobús, por ejemplo:
Aquí he introducido en el viaje con identificador número 1 que hay 45 asientos disponibles.
Después de esto hacemos la venta de un billete, por ejemplo el billete que corresponde al asiento 14:
Al realizar esta consulta si vamos al campo de disponibilidad_actual veremos que 45 ha cambiado a 44, ya que se activó el trigger ‘actualizar_disponibilidad’.
Las ventajas de utilizar triggers es que se pueden programar diferentes casos de uso y que la integridad de la base de datos se mantenga, es decir, nos da la opción de crear rutinas que ejecuten determinadas acciones de manera automática sin la necesidad de hacerlo manualmente. Sería muy válido para validaciones de cualquier tipo de información o para hacer algún seguimiento de movimientos de la base de datos.
Otro ejemplo muy interesante como acabo de comentar en las validaciones es el determinar una mayoría de edad antes de realizar una sentencia, por ejemplo:
Después de ver los ejemplos de creación de disparadores debemos conocer cómo se accede a la información del trigger. Para ello utilizaremos las sentencias:
Para ver los disparadores que hay en nuestra base de datos:
SHOW TRIGGERSEn cambio si queremos ver la información del trigger que se ha creado en el momento:
SHOW CREATE TRIGGER alumno_BU_triggerPor último después de haber visto cómo se crean y visualizan los triggers vamos a ver cuál es la sentencia utilizada para eliminar un disparador que tengamos en nuestra base de datos.
Para ello sólo tenemos que utilizar el comando típico de eliminación: drop.
DROP TRIGGER [IF_EXISTS]nombre_disparador
