MySQL Triggers-Bitacora
https://www.hoclabs.com/2017/05/14/mysql-triggers/
https://www.hoclabs.com/blog/
May 14, 2017Hiram GuerreroMySQLNo Comments
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
