Disparadores de MySQL para Automatización

Introducción

En este laboratorio, aprenderá a utilizar disparadores (triggers) de MySQL para automatizar acciones en la base de datos. Los disparadores son programas almacenados que se ejecutan automáticamente en respuesta a eventos específicos en una tabla, como operaciones de INSERT, UPDATE o DELETE.

Creará una tabla products para almacenar información de artículos y una tabla product_logs para auditar cambios. Luego, implementará un disparador que agregará automáticamente una entrada de registro en product_logs cada vez que se inserte un nuevo producto en la tabla products. El laboratorio también lo guiará a través de la prueba, actualización y eliminación del disparador.

Configurar la Base de Datos y las Tablas

Antes de crear un disparador, necesita una base de datos y las tablas necesarias. En este paso, creará una base de datos llamada mydatabase y dos tablas: products para almacenar datos de productos y product_logs para almacenar registros de auditoría.

Primero, abra la terminal desde su escritorio.

Conéctese al servidor MySQL como usuario root. Dado que este es un entorno de laboratorio, puede usar sudo para conectarse sin contraseña.

sudo mysql -u root

Una vez conectado, verá el prompt de MySQL (mysql>).

Ahora, cree la base de datos mydatabase y cambie a ella. La cláusula IF NOT EXISTS evita un error si la base de datos ya existe.

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

A continuación, cree la tabla products. Esta tabla almacenará el ID, nombre y precio de cada producto.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Ahora, cree la tabla product_logs. Esta tabla almacenará un registro de cada nuevo producto agregado.

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    action_description VARCHAR(255),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Puede verificar que ambas tablas se crearon correctamente ejecutando:

SHOW TABLES;

Debería ver las tablas products y product_logs listadas en la salida.

+--------------------+
| Tables_in_mydatabase |
+--------------------+
| product_logs       |
| products           |
+--------------------+
2 rows in set (0.00 sec)

Ahora que la base de datos y las tablas están listas, puede continuar con el siguiente paso.

Crear y Probar un Disparador

Con las tablas listas, ahora puede crear un disparador. Este disparador se activará después de que se inserte una nueva fila en la tabla products y agregará una entrada correspondiente a la tabla product_logs.

Cambie a su base de datos.

USE mydatabase;

Ahora, cree el disparador. El comando DELIMITER se utiliza para cambiar el delimitador estándar (;) a // para que el punto y coma dentro del cuerpo del disparador no se interprete como el final de la sentencia CREATE TRIGGER.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name));
END//

DELIMITER ;

Analicemos esta sentencia:

• CREATE TRIGGER products_after_insert: Define un nuevo disparador con el nombre products_after_insert.
• AFTER INSERT ON products: Especifica que el disparador se activará después de un evento INSERT en la tabla products.
• FOR EACH ROW: Indica que la lógica del disparador se ejecutará por cada fila que se inserte.
• BEGIN...END: Encierra las sentencias SQL del disparador.
• NEW.id, NEW.name: La palabra clave NEW se refiere a la fila que acaba de ser insertada. NEW.id es el id del nuevo producto, y NEW.name es su nombre.

Ahora, probemos el disparador insertando un nuevo producto.

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);

Esta operación INSERT debería haber activado el disparador. Para confirmarlo, revise el contenido de la tabla product_logs.

SELECT * FROM product_logs;

Debería ver un registro en la tabla product_logs, que fue creado automáticamente por el disparador. La salida será similar a esta (el log_id y log_time variarán):

+--------+------------+-----------------------------+---------------------+
| log_id | product_id | action_description          | log_time            |
+--------+------------+-----------------------------+---------------------+
|      1 |          1 | New product added: Laptop   | 2024-05-27 10:30:00 |
+--------+------------+-----------------------------+---------------------+
1 row in set (0.00 sec)

Esto confirma que su disparador está funcionando correctamente.

Actualizar la Lógica del Disparador

Sus requisitos pueden cambiar con el tiempo. Por ejemplo, podría querer registrar más detalles. En este paso, actualizará el disparador para que también registre el precio del nuevo producto.

En MySQL, no puede modificar directamente un disparador. Primero debe eliminar el disparador existente y luego crear uno nuevo con la lógica actualizada.

Cambie a su base de datos.

USE mydatabase;

Primero, elimine el disparador existente. La cláusula IF EXISTS evita un error si el disparador ya ha sido eliminado.

DROP TRIGGER IF EXISTS products_after_insert;

Ahora, recree el disparador con la lógica actualizada. Esta nueva versión incluirá el precio del producto en la action_description.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name, ' with price ', NEW.price));
END//

DELIMITER ;

Para probar el disparador actualizado, inserte otro producto.

INSERT INTO products (name, price) VALUES ('Mouse', 25.00);

Ahora, consulte la tabla product_logs para ver todas las entradas del registro.

SELECT * FROM product_logs;

La salida ahora debería mostrar dos entradas de registro. La segunda entrada, para el 'Mouse', debería incluir el precio en su descripción, confirmando que su disparador actualizado está funcionando.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

Ha actualizado el disparador con éxito.

Eliminar el Disparador

Si un disparador ya no es necesario, debe eliminarlo para evitar procesamiento innecesario y posibles problemas de mantenimiento. En este paso final, eliminará el disparador products_after_insert.

Cambie a su base de datos.

USE mydatabase;

Para eliminar el disparador, utilice la sentencia DROP TRIGGER.

DROP TRIGGER IF EXISTS products_after_insert;

Puede verificar que el disparador ha sido eliminado ejecutando el comando SHOW TRIGGERS.

SHOW TRIGGERS;

Este comando debería producir un conjunto vacío, confirmando que el disparador ya no existe.

Empty set (0.00 sec)

Para estar completamente seguro, inserte un producto más.

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);

Ahora, revise la tabla product_logs.

SELECT * FROM product_logs;

La tabla aún debería contener solo las dos primeras entradas de registro. El producto 'Keyboard' no debería tener una entrada de registro, porque el disparador fue eliminado antes de la inserción.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

Esto confirma que el disparador ha sido eliminado con éxito. Ahora puede salir del shell de MySQL.

exit;

Resumen

En este laboratorio, ha aprendido los fundamentos del uso de disparadores (triggers) de MySQL para la automatización. Ha configurado con éxito un entorno de base de datos, creado tablas e implementado un disparador para registrar eventos de inserción de datos. También ha practicado el ciclo de vida completo de un disparador, incluyendo su creación, prueba, actualización y eliminación.

Estas habilidades son esenciales para tareas como la creación de pistas de auditoría, la aplicación de reglas de negocio complejas y el mantenimiento automático de la integridad de los datos dentro de su base de datos.