MySQL Triggers

Ahora toca publicar algo de MySQL, así que empezaremos con algo de triggers! Si aún no los has utilizado sigue leyendo porque seguro que aprendes algo nuevo y muy útil a la hora de trabajar con BD, en particular con MySQL.

¿Trigger?

Un trigger (disparador) es una función que se ejecuta antes o después de algún cambio (INSERT, UPDATE, DELETE) en una tabla.

Casos de uso

• Para validar la información antes de insertar los datos en una tabla

• Llevar una bitácora de cambios realizados

• Procesar y operar datos antes de registrar

Práctica

Realizaremos un ejercicio básico donde registraremos los actualizaciones realizadas sobre una tabla de usuarios. Para ello definiremos dos tablas, usuarios y bitacora, la primera sera nuestra tabla principal donde almacenaremos la información de los usuarios, en la segunda llevaremos un registro de todos los cambios realizados.

CREATE TABLE `usuarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `nombre` varchar(255) DEFAULT NULL,
  `telefono` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `bitacora` (
  `id_usuario` int(11) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `nombre` varchar(255) DEFAULT NULL,
  `telefono` varchar(255) DEFAULT NULL,
  `fecha` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A continuación tenemos el código de nuestro trigger, el cual llamaremos after_usuarios_update:

CREATE TRIGGER after_usuarios_update 
   AFTER UPDATE ON usuarios
   FOR EACH ROW 
BEGIN
   INSERT INTO bitacora
   SET 
    id_usuario = OLD.id
    nombre = OLD.nombre,
    email = OLD.email,
    password = OLD.password,
    telefono = OLD.telefono;
END

Una vez ejecutado nuestro trigger, deben ingresar al menos un registro en la tabla usuarios, después deben cambiar al menos un campo de dicho registro, una vez realizado esto verán como la tabla bitacora empieza a ser actualizada en automático con los antiguos valores del registro actualizado.

Ahora que ya crearon y ejecutaron el trigger creado expliquemos como se deben declarar:

• CREATE TRIGGER after_usuarios_update (definimos el nombre del trigger)
• AFTER UPDATE ON usuarios (momento en que el trigger es accionado: Después de actualizar la tabla usuarios)
• FOR EACH ROW (para todas las filas donde hubo algún cambio)
• BEGIN (donde inicia el código sql de la acción a realizar)
• En nuestro caso tenemos un INSERT a la tabla bitacora donde definimos que campos vamos a llenar, el prefijo OLD inidica lo que había en nuestra tabla de usuarios en dicho campo
• END (donde acaba el código sql de la acción a realizar)

Para nuestro segundo ejemplo utilizaremos otra tabla bitácora donde además de almacenar el valor de lo que había antes de actualizar nuestra tabla de usuarios también almacenaremos los nuevos valores ingresados. Para ello modificaremos la estructura de nuestra tabla bitacora como sigue:

CREATE TABLE `bitacora` (
  `id_usuario` int(11) NOT NULL,
  `old_email` varchar(255) DEFAULT NULL,
  `old_password` varchar(255) DEFAULT NULL,
  `old_nombre` varchar(255) DEFAULT NULL,
  `old_telefono` varchar(255) DEFAULT NULL,
  `new_email` varchar(255) DEFAULT NULL,
  `new_password` varchar(255) DEFAULT NULL,
  `new_nombre` varchar(255) DEFAULT NULL,
  `new_telefono` varchar(255) DEFAULT NULL,
  `fecha` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 Después modificaremos nuestro trigger para que sea capaz de almacenar los nuevos y viejos valores:

CREATE TRIGGER after_usuarios_update 
   AFTER UPDATE ON usuarios
   FOR EACH ROW 
BEGIN
   INSERT INTO bitacora
   SET 
    id_usuario = OLD.id,
    old_nombre = OLD.nombre,
    old_email = OLD.email,
    old_password = OLD.password,
    old_telefono = OLD.telefono,
    new_nombre = NEW.nombre,
    new_email = NEW.email,
    new_password = NEW.password,
    new_telefono = NEW.telefono;
END

Una vez ejecutado el nuevo el trigger y haber realizado los cambios sobre la bitacora pueden actualizar su tabla usuarios para observar los cambios.

Si llegaste hasta acá quizá te preguntes sobre las palabras clave OLD y NEW, o como puedes ver/actualizar/eliminar los triggers definidos en nuestra BD.

De acuerdo con la doc oficial: Dentro del cuerpo de los triggers las palabras clave OLD y NEW te permiten acceder a las columnas de las filas afectadas por el mismo, estás son extensiones para los triggers de MySQL y no hay diferencia entre usarlas como mayúsculas o minúsculas.

Instrucción para ver los triggers definidos: SHOW TRIGGERS;

Instrucción para mostrar la definición de un trigger: SHOW CREATE TRIGGER after_usuarios_update;

Instrucción para eliminar un trigger: DROP TRIGGER after_usuarios_update;

Hasta aquí la primera parte de esta serie de triggers en MySQL, si tienes alguna duda, consejo o aclaración por favor escribela en los comentarios.

El repositorio con el código de todos los ejercicios del blog (incluído este) lo puedes encontrar en el siguiente repo: https://github.com/eichgi/blog-hoclabs