Triggers en MySQL

Los grandes motores de búsqueda como MySQL, PostgreSQL, SQL Server u Oracle, disponen de una cantidad de funcionalidades realmente extensa. Una de esas funcionalidades son los desencadenadores o triggers, en esta entrada hablaremos sobre ellos.

Triggers en Mysql

Mi experiencia con los triggers proviene principalmente de SQL Server, plataforma en la que he hecho un uso bastante extenso de ellos, y me atrevería a asegurar que para algunos programadores, en según que condiciones, los triggers pueden abrir un mundo nuevo de posibilidades, facilitando enormemente la realización de algunas tareas que de no ser por éstos tendrían una resolución bastante más aparatosa.

Qué son los triggers en MySQL

Los triggers (o desencadenadores) son pequeños programas creados por el usuario que se invocan de forma automática por el propio motor de bases de datos al producirse ciertos eventos, lo que nos permite capturarlos, controlarlos e intervenir en ellos.

En MySQL los eventos que podemos capturar son los relacionados con las sentencias INSERT, UPDATE y DELETE.

Precauciones en el uso de triggers

Antes de entrar de lleno con ellos, es importante conocer cuales son los inconvenientes más comunes derivados de su uso para así evitarlos o cuanto menos minimizarlos.

El primero de ellos es el rendimiento. Ejecutar el código programado en el trigger conlleva un consumo de recursos que por norma general suele ser muy superior al de la sentencia que lo origina. Por lo tanto deberemos tener en cuenta esa carga y optimizarla para evitar posibles problemas como ralentización del sistema o interbloqueos.

El segundo inconveniente a tratar es la visibilidad. Hablamos de una porción de código que se encuentra completamente separada del resto, lo que en ocasiones puede provocar que nos olvidemos que está ahí, o que lo desconozcamos por completo si nos enrolamos en un nuevo proyecto.

Y una recomendación derivada del punto anterior, es poner un extra de atención para asegurar su correcto funcionamiento, o de lo contrario podemos encontrarnos con resultado indeseados mucho tiempo después de su activación.

Por último debemos destacar el efecto global de los triggers, que actuarán siempre que ocurra el evento para el que han sido definidos, independientemente de la aplicación, usuario o cualquier otra variable de sistema que se nos ocurra. Esta condición es intrínseca los triggers y es básicamente lo que buscamos, pero debemos recordarlo si en algún momento actuamos sobre la base de datos desde un lugar diferente al habitual.

Cuándo utilizar triggers

Desde mi punto de vista los triggers tienen dos escenarios principales de uso:

El primero es cuando no podemos intervenir en el código fuente de la aplicación que trabaja sobre la base de datos sobre la que queremos actuar o reaccionar a sus eventos.

El segundo es cuando a pesar de disponer del código, éste pertenece a un software desarrollado por terceros y existe una probabilidad relativamente alta de aplicar actualizaciones o instalar pluggins, y hemos decidido modificar el código fuente lo menos posible para facilitar este tipo de tareas.

Un ejemplo muy habitual son integraciones de software en las que al menos uno de los programas que intervienen es de código cerrado. En este tipo de situaciones los triggers nos permiten capturar los eventos que modifican la base de datos y actuar en consecuencia, evitando crear tareas programadas y e incluso tablas de integración.

Cuándo no utilizar trigger

Cuando tenemos acceso al código fuente de la aplicación que trabaja sobre la base de datos que nos interesa y no tenemos que mantener ningún tipo de compatibilidad con cambios como nuevas versiones, no existe razón alguna para utilizar triggers. En estos casos será mejor trabajar sobre el código de la aplicación y así evitaremos los inconvenientes de uso de los triggers.

Bien sea para registrar logs de acciones, generar registros de integración, realizar validaciones pre o post inserción o cualquier otra tarea que se nos ocurra, siempre será más eficiente, más visible y por lo tanto sencillo para el programador delegar esa responsabilidad en la propia aplicación.

Usando los triggers

Muy bien, ya hemos tomado las consideraciones necesarias y hemos decidido utilizar triggers, veamos ahora cómo crearlos.

Sentencias sobre las que podemos crear triggers

Ya lo adelantábamos al comienzo del artículo, podemos crear triggers que actúen sobre las sentencias:

  • INSERT
  • UPDATE
  • DELETE

De esta forma podremos intervenir cada vez que se inserte, modifique o elimine un registro.

Cada trigger se define para un tipo de evento, de forma que si queremos controlar los tres eventos para una determinada tabla deberemos crear tres triggers.

También es importante mencionar que los triggers actúan registro a registro, es decir, supongamos que tenemos una sentencia UPDATE que modifica 100 registros, el trigger se ejecutará 100 veces, una por cada registro modificado

BEFORE y AFTER

Y como complemento al tipo de sentencia, podemos decidir si intervenir antes o después de que ésta realice los cambios. Así un trigger AFTER INSERT nos permitirá actuar una vez que el nuevo registro este insertado, y un trigger BEFORE DELETE lo hará antes de eliminar un registro.

La combinación del tipo de sentencia con BEFORE o AFTER nos ofrecen mucha versatilidad a la hora de diseñar nuestros triggers.

NEW y OLD

NEW y OLD son las palabras reservadas de MySQL para acceder a los valores de los registros sobre los que estamos trabajando.

  • OLD nos permite acceder a los datos previos a la modificación realizada por la sentencia.
  • NEW nos permite acceder a los nuevos datos que propone la sentencia.

La sintaxis es muy sencilla, OLD.campo o NEW.campo, dónde campo es el nombre del campo de la tabla al que queremos acceder.

Cómo crear un trigger en MySQL

Y una buena forma de aprender a crear un triggers es mediante un ejemplo, así que veamos uno. Supongamos que tenemos la tabla brands que forma parte de la base de datos de un software propietario:

+----+----------+---------+
| ID | Name     | Segment |
+----+----------+---------+
|  1 | Opel     | Mass    |
|  2 | Renault  | Mass    |
|  3 | Ford     | Mass    |
|  4 | Audi     | Premium |
|  5 | Mercedes | Premium |
|  6 | Porche   | Luxury  |
|  7 | Ferrari  | Luxury  |
+----+----------+---------+

Y queremos registrar los cambios que se producen para integrarlos con otro sistema, para lo que hemos creado una tabla en la que registraremos cualquier cambio que se produzca en la tabla brands. La nueva tabla la hemos llamado integration, y tiene los siguientes campos:

+----+--------+----------+--------+---------------------+-----------+---------------+------------+
| ID | Object | ObjectID | Action | DateCreated         | Processed | DateProcessed | ErrorCount |
+----+--------+----------+--------+---------------------+-----------+---------------+------------+

Para registrar todos los cambios que se produzcan en la tabla brands deberemos generar tres triggers:

  • Uno para registrar los nuevos registros: AFTER INSERT.
  • Uno para registrar los cambios: AFTER UPDATE.
  • Uno para registrar las eliminaciones: BEFORE DELETE.

En esta entrada crearemos únicamente el trigger para la sentencia INSERT. Crear el resto de triggers se hace de forma muy similar, y en caso de que estés interesado en aprender será una buena práctica.

Así que veamos como crear el trigger, que será el encargado de registrar las nuevas marcas:

delimiter //
CREATE TRIGGER brandsInsertLog AFTER INSERT ON brands
FOR EACH ROW
BEGIN

	INSERT INTO integration (`Object`, `ObjectID`, `Action`, `DateCreated`, `Processed`, `DateProcessed`, `ErrorCount`) 
	VALUES ('brands', NEW.ID, 'insert', now(), 0, null, 0);

END;//
delimiter;

De forma que si ejecutásemos una sentencia de de inserción sobre la tabla brands:

INSERT INTO `osticket`.`brands` (`ID`, `Name`, `Segment`)
VALUES (8, 'Citroen', 'Mass');

Se registraría automáticamente la entrada correspondiente en la tabla integration:

+----+--------+----------+--------+---------------------+-----------+---------------+------------+
| ID | Object | ObjectID | Action | DateCreated         | Processed | DateProcessed | ErrorCount |
+----+--------+----------+--------+---------------------+-----------+---------------+------------+
|  1 | brands |        8 | insert | 2019-09-21 08:20:07 |         0 | NULL          |          0 |
+----+--------+----------+--------+---------------------+-----------+---------------+------------+

Así de sencillo. Espero que esta entrada te haya servido de ayuda y si es así un comentario siempre es de agradecer.

Créditos, referencias y artículos relacionados

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *