Desarrollo de una Bitácora usando Triggers en MySQL (Fedora 19)

En el presente material se contemplan 4 etapas las cuales son:

1. Creación de una Base de Datos (que solo consta de 2 tablas, Artículos y Bitácora. La tabla de bitácora, es la tabla que controlará los procesos u operaciones que se realicen sobre la tabla Artículos (Insertar, Modificar, Eliminar), también se controlará el usuario que realice dichas operaciones, así mismo la Fecha y hora en que realizó alguna operación.).
2. Creación de un usuario en MySQL (para administrar quien realiza operaciones en la B.D.).
3. Creación de Triggers (Insertar, Modificar y Eliminar).
4. Comprobación (en foto-capturas y en video).

Cabe aclarar que el material presentado es de una forma básica y las tablas como las operaciones a registradas en la Bitácora dependerán de la Base de Datos que deseen crear.

Sin mas preámbulo se procede con la primera etapa.

Creación de la B.D Control

1.- Se pasa a abrir una terminal en Fedora y se accede a mysql.

“ mysql -u root -p “

2.- Se pasa a crear la base de datos la cual llevará el nombre de control. Para ello se usa la siguiente instrucción:

“ create database control “

3.- Se accede a la base de datos.

“ use control; “

4.- Se pasa a crear la tabla de Artículos con los siguientes campos: [id_articulo, nombre_articulo, precio].

“ create table articulos (id_articulo int (20) not null primary key, nombre_articulo varchar (20), precio varchar (20))engine=innodb; “

5.- Ahora se crea la tabla de bitácora, por lo cual, se harán uso de los siguientes campos: [id_bitacora, id_articulo, nombre_nuevo, nombre_viejo, precio_nuevo, precio_viejo, usuario, fecha, accion]. Los campos con " _nuevo  y  _viejo ", son usados por si un usuario realiza una modificación sobre los artículos. Esto se explicará más adelante con los Triggers. Ahora bien para crear la tabla Bitácora se hace lo siguiente:

“ create table bitacora (id_bitacora int (30) auto_increment primary key, id_articulo int (20), nombre_nuevo varchar (20), nombre_viejo varchar (20), precio_nuevo varchar (20), precio_viejo varchar (20), usuario varchar (30), fecha datetime, accion varchar (15))engine=innodb;

Ya terminada la base de datos con sus respectivas tablas, se pasan a crear los Triggers, para que se ejecuten a la hora de una inserción, modificación o eliminación sobre la tabla artículos.

Creación de los Triggers

Antes de comenzar a crear los Triggers o disparadores, se explicará la sintaxis que debe de llevar, algo como lo siguiente:

CREATE TRIGGER nombre_trigger BEFORE|AFTER INSERT|UPDATE|DELETE

ON nombre_tabla

FOR EACH ROW

BEGIN

Funcion_del_Trigger

DONDE:

CREATE TRIGGER= instrucción para crear el trigger

nombre_trigger= nombre que se le desee dar al trigger

BEFORE, AFTER = representa la forma de ejecución Antes o Después

INSERT, UPDATE, DELETE = función en la que se ejecutará dicho trigger, (Antes o Después de realizar una inserción, modificación o eliminación.

Nombre_tabla = Nombre de la tabla sobre la que tendrá acción el Trigger.

FOR EACH ROW = Sintaxis de default, para decir que tendrá ejecución para cada uno de los datos de la fila.

BEGIN = Indica el comienzo de la acción, esta instrucción varía dependiendo de la versión de mysql, en versiones antiguas a la versión 5.1 se usa esta instrucción, y en versiones posteriores, se omite.

Funcion_del_Trigger = La tarea que realizará dicho trigger.

Los Triggers en la parte de Funcion_del_Trigger suelen manejar palabras como: NEW. Y OLD. Eso hace referencia a la captura de un dato Nuevo o uno Viejo.

Dicho lo Anterior se pasará a realizar los Triggers.

1.- Primero se creará el Trigger que capture ciertos datos a la hora de insertar un nuevo registro en la tabla de Artículos, dichos datos, se alojarán automaticamente en la tabla de Bitácora. Para ello se crea de la siguiente manera.

“ create trigger insertar after insert

on articulos

for each row

insert into bitacora (id_articulo, nombre_nuevo, precio_nuevo, usuario, fecha, accion) values (new.id_articulo, new.nombre_articulo, new.precio, user(), now(), 'Inserto'); “

NOTA: El Trigger indica que a la hora de realizar el alta de un nuevo artículo, capture en la tabla bitácora, el Id_articulo, nombre_articulo, precio, usuario (user), la fecha y hora (now), y que Insertó un nuevo registro.

2.- Se crea el Trigger de Actualizar.

“ create trigger actualizar after update

on articulos

for each row

insert into bitacora (id_articulo, nombre_nuevo, nombre_viejo, precio_nuevo, precio_viejo, usuario, fecha, accion) values (old.id_articulo, new.nombre_articulo, old.nombre_articulo, new.precio, old.precio, user(), now(), 'Modifico'); “

NOTA: En este Trigger, se almacenan tanto los nombres nuevos como los viejos, así mismo en sus precios, ya que es una actualización, se debe controlar el nombre anterior o su precio anterior, así como quien realizó dicha operación.

3.- Para terminar se crea el Trigger de Eliminar.

“create trigger eliminar after delete

on articulos

for each row

insert into bitacora (id_articulo, nombre_viejo, precio_viejo, usuario, fecha, accion) values (old.id_articulo, old.nombre_articulo, old.precio, user(), now(), 'Elimino'); “

NOTA: Este Trigger tiene como función solo capturar datos viejos por lo cual solo se utilizan los OLD, el New en este Trigger no funciona por sintaxis.

Creación de un Usuario.

Se crea un usuario, para verificar que los disparadores verdaderamente capturen el nombre del usuario.

1.- Se crea el usuario “manuel” para una pequeña comprobación, el usuario será local (localhost), si se desea poner en red, basta con agregar la IP correspondiente a la PC que establecerá conexión Al Servidor.

“ CREATE USER 'manuel'@'localhost' IDENTIFIED BY '123'; “

2.- Se otorgan los respectivos privilegios sobre la base de datos.

“ GRANT ALL ON * . * TO 'manuel'@'localhost' IDENTIFIED BY '123'; “

3.- Se pasan a actualizar los privilegios previamente asignados.

“ flush privileges “

Comprobación

1.- Se abre una terminal, y se pasa a acceder a mysql con el usuario que se creó anteriormente.

“ mysql -u manuel -p ”

2.- Se pasará a acceder a la base de datos control y se insertará un nuevo registro a la tabla artículos, luego se pasará a visualizar la tabla bitácora, para corroborar que si se ejecutó el disparador, con los datos correspondientes a la Inserción del Registro.

2.- Se pasa a modificar el registro anexado y se cambiará el nombre del producto por Lap-top y el precio por $15,000.00, luego se realiza una nueva consulta en bitácora para corroborar que los datos se han almacenado, tanto el nombre y precio nuevo.

3.- Por último se realizará una eliminación, y se debe de almacenar los datos correspondientes en la tabla bitácora.

Con esto queda comprobada la funcionalidad de los disparadores.

Comprobación en video de las instrucciones usadas  anteriormente, para corroborar lo que aquí se expone.