Cómo administrar y usar activadores de bases de datos de MySQL en Ubuntu 18.04

El autor seleccionó la Apache Software Foundation para recibir una donación como parte del programa Write for DOnations.

Introducción

En MySQL, un activador es un comando SQL definido por el usuario que se invoca automáticamente durante una operación INSERT, DELETE o UPDATE. El código de activación se asocia con una tabla y se destruye una vez que se elimina una tabla. Puede especificar un tiempo de acción de activación y establecer si esta se producirá antes o después del evento de base de datos definido.

Los activadores tienen varias ventajas. Por ejemplo, puede usarlos para generar el valor de una columna derivada durante una instrucción INSERT. Otro caso de uso tiene que ver con la aplicación de la integridad referencial, en el que puede usar un activador para guardar un registro en varias tablas relacionadas. Entre otras ventajas, se incluyen el registro de las acciones del usuario para realizar una auditoría de las tablas, así como la copia en vivo de datos en diferentes esquemas de bases de datos por motivos de redundancia para evitar un único punto de fallo.

También puede usar activadores para mantener las reglas de validación en el nivel de base de datos. Esto le permite compartir la fuente de datos en varias aplicaciones sin infringir la lógica de negocios. Con esto, se reducen en gran medida los recorridos de ida y vuelta al servidor de base de datos, lo cual a su vez mejora el tiempo de respuesta de sus aplicaciones. Debido a que en el servidor de base de datos se ejecutan activadores, se pueden aprovechar recursos mejorados de este, como la memoria RAM y el CPU.

En este tutorial, creará, usará y eliminará diferentes tipos de activadores en su base de datos de MySQL.

Requisitos previos

Antes de comenzar, asegúrese de contar con lo siguiente:

• Un servidor de Ubuntu 18.04 configurado conforme a la Configuración inicial de servidores con Ubuntu 18.04, con un usuario sudo no root.
• Una base de datos de MySQL activa en su servidor, conforme a Cómo instalar MySQL en Ubuntu 18.04.
• Credenciales de cuenta de usuario root para su base de datos de MySQL.

Paso 1: Crear una base de datos de ejemplo

En este paso, creará una base de datos de clientes de ejemplo con varias tablas para demostrar el funcionamiento de los activadores de MySQL.

Para obtener más información sobre consultas de MySQL, lea nuestra Introducción a las consultas de MySQL.

Primero, inicie sesión en su servidor MySQL como root:

mysql -u root -p

Introduzca su contraseña root de MySQL cuando se le solicite y presione INTRO para continuar. Cuando visualice el intérprete de comandos de mysql>, ejecute el siguiente comando para crear una base de datos test_db:

Create database test_db;

Output
Query OK, 1 row affected (0.00 sec)

A continuación, realice un cambio a test_db con lo siguiente:

Use test_db;

Output
Database changed

Comience creando una tabla customers. En esta tabla se almacenarán los registros de los clientes, incluidos customer_id, customer_name y level. Habrá dos niveles de cliente: BASIC y VIP.

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

Output
Query OK, 0 rows affected (0.01 sec)

Ahora, añada algunos registros a la tabla customers. Para hacer esto, ejecute los siguientes comandos uno por uno:

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

Verá el siguiente resultado después de ejecutar cada uno de los comandos INSERT:

Output
Query OK, 1 row affected (0.01 sec)

Para asegurarse de que los registros de ejemplo se hayan insertado correctamente, ejecute el comando SELECT:

Select * from customers;

Output
+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

También creará otra tabla para contener información relacionada con la cuenta customers. Esta tabla contendrá un campo customer_id y un campo status_notes.

Ejecute el siguiente comando:

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

A continuación, creará una tabla sales. En esta tabla, se almacenarán datos de ventas relacionados con los diferentes clientes a través de la columna customer_id:

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

Output
Query OK, 0 rows affected (0.01 sec)

En los próximos pasos, añadirá datos de ejemplo a los de sales mientras prueba los activadores. A continuación, cree una tabla audit_log para registrar las actualizaciones realizadas en la tabla sales cuando implemente el activador AFTER UPDATE en el paso 5:

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

Output
Query OK, 0 rows affected (0.02 sec)

Una vez establecidas la base de datos test_db y las cuatro tablas, procederá a trabajar con los diferentes activadores de MySQL en su base de datos.

Paso 2: Crear un activador “Before Insert”

En este paso, examinará la sintaxis de un activador de MySQL antes de aplicar esta lógica para crear un activador BEFORE INSERT que valide el campo sales_amount cuando se inserten datos en la tabla sales.

La sintaxis general para crear un activador de MySQL se muestra en el siguiente ejemplo:

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

En la estructura del activador se incluye lo siguiente:

DELIMITER //: el delimitador predeterminado de MySQL es ;, es necesario cambiarlo por algo diferente para que MySQL trate las siguientes líneas como un comando hasta que alcance su delimitador personalizado. En este ejemplo, el delimitador se cambió a // y luego al final se redefinió el delimitador ;.

[TRIGGER_NAME]: un activador debe contar con un nombre y aquí es donde debe incluir el valor.

[TRIGGER TIME]: un activador se puede invocar en diferentes intervalos. En MySQL se le permite definir si el activador se iniciará antes o después de una operación de base de datos.

[TRIGGER EVENT]: los activadores solo se pueden invocar con operaciones INSERT, UPDATE y DELETE. Aquí puede usar cualquier valor dependiendo de lo que quiera lograr.

[TABLE]: cualquier activador que cree en su base de datos de MySQL debe estar asociado a una tabla.

FOR EACH ROW: con esta instrucción se ordena a MySQL ejecutar el código de activación para cada fila afectada por el activador.

[TRIGGER BODY]: el código que se ejecuta cuando se invoca el activador se conoce como cuerpo del activador. Puede constar de una sola instrucción SQL o de varios comandos. Tenga en cuenta que si ejecuta varias instrucciones SQL en el cuerpo del activador, debe ajustarlas entre un bloque BEGIN... END.

Ahora, creará su primer activador BEFORE INSERT. Este activador estará asociado con la tabla sales y se invocará antes de que se inserte un registro para validar sales_amount. La función del activador es verificar si el campo sales_amount que se inserta en la tabla “sales” es mayor que 10000 y mostrar un error si esto se evalúa a “true”.

Asegúrese de haber iniciado sesión en el servidor de MySQL. Luego, introduzca los siguientes comandos MySQL uno por uno:

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
END IF//
DELIMITER ;

Usará la instrucción IF...THEN...END IF para evaluar si la cantidad que se proporciona durante la instrucción INSERT se encuentra dentro de su rango. Con el activador se puede extraer el nuevo valor sales_amount proporcionado con la palabra clave NEW.

Para mostrar un mensaje de error genérico, utilice las siguientes líneas a fin de informar al usuario sobre el error:

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

A continuación, inserte un registro con un sales_amount de 11000 en la tabla sales para verificar si con el activador se detendrá la operación:

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

Output
ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

Este error muestra que el código de activación funciona según lo previsto.

Ahora, pruebe un nuevo registro con un valor de 7500 para verificar si el comando tendrá éxito:

Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

Debido a que el valor se encuentra dentro del rango recomendado, verá el siguiente resultado:

Output
Query OK, 1 row affected (0.01 sec)

Para confirmar que se insertaron los datos, ejecute el siguiente comando:

Select * from sales;

En el resultado, se confirma que los datos se encuentran en la tabla:

Output
+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

En este paso, probó activadores para validar datos antes de su inserción en una base de datos.

A continuación, trabajará con el activador AFTER INSERT para guardar información relacionada en diferentes tablas.

Paso 3: Crear un activador “After Insert”

Los activadores AFTER INSERT se ejecutan cuando se insertan registros correctamente en una tabla. Esta funcionalidad puede utilizarse para ejecutar automáticamente otras lógicas relacionadas con los negocios. Por ejemplo, en una aplicación bancaria, con un activador AFTER INSERT se puede cerrar una cuenta de préstamo cuando un cliente termina de pagarlo. El activador puede controlar todos los pagos introducidos en una tabla de transacciones y cerrar el préstamo automáticamente una vez que el saldo llegue a cero.

En este paso, trabajará con su tabla customer_status usando un activador AFTER INSERT para ingresar registros de clientes relacionados.

Para crear el activador AFTER INSERT, introduzca los siguientes comandos:

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

Output
Query OK, 0 rows affected (0.00 sec)

Aquí, indica que MySQL guarde otro registro en la tabla customer_status una vez que se inserte un nuevo registro de cliente en la tabla customers.

Ahora, inserte un nuevo registro en la tabla customers para confirmar que se invocará su código de activación.

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

Output
Query OK, 1 row affected (0.01 sec)

Debido a que el registro se insertó correctamente, compruebe que se haya introducido un nuevo registro de estado en la tabla customer_status:

Select * from customer_status;

Output
+-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

En el resultado se confirma que el activador se ejecutó correctamente.

El activador AFTER INSERT es útil para controlar el ciclo de vida de un cliente. En un entorno de producción, las cuentas de los clientes pueden atravesar diferentes etapas, como la apertura, la suspensión y el cierre.

En los siguientes pasos, trabajará con activadores UPDATE.

Paso 4: Crear un activador “Before Update”

Los activadores BEFORE UPDATE son similares a los BEFORE INSERT; la diferencia radica en el momento en que se invocan. Puede usar el activador BEFORE UPDATE para verificar una lógica de negocios antes de que se actualice un registro. Para probar esto, usará la tabla customers en la que ya insertó datos.

Dispone de dos niveles para sus clientes en la base de datos. En este ejemplo, una vez que la cuenta de un cliente asciende al nivel VIP, ya no es posible que descienda al nivel BASIC. Para aplicar tal regla, creará un activador BEFORE UPDATE que se ejecutará antes de la instrucción UPDATE, como se muestra a continuación. Si un usuario de la base de datos intenta hacer que un cliente descienda al nivel BASIC desde el nivel VIP, se activará una excepción definida por el usuario.

Introduzca los siguientes comandos SQL uno por uno para crear el activador BEFORE UPDATE:

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
END IF //
DELIMITER ;

Utilice la palabra clave OLD para capturar el nivel que el usuario proporciona cuando ejecuta el comando UPDATE. Una vez más, utilice la instrucción IF...THEN…END IF`` para indicar una instrucción de error genérico al usuario.

Luego, ejecute el siguiente comando SQL con el que se intenta bajar el nivel de la cuenta de un usuario asociado con el customer_id de 3:

Update customers set level='BASIC' where customer_id='3';

Verá el siguiente resultado, en el que se muestra SET MESSAGE_TEXT:

Output
ERROR 1644 (45000): A VIP customer can not be downgraded.

Si ejecuta el mismo comando en un cliente de nivel BASIC e intenta que la cuenta ascienda al nivel VIP, el comando se ejecutará correctamente:

Update customers set level='VIP' where customer_id='1';

Output
Rows matched: 1  Changed: 1  Warnings: 0

Usó el activador BEFORE UPDATE para implementar una regla comercial. Ahora, usará un activador AFTER UPDATE para registros de auditoría.

Paso 5: Crear un activador “After Update”

Un activador AFTER UPDATE se invoca una vez que se actualiza correctamente el registro de una base de datos. Este comportamiento hace que el activador sea adecuado para los registros de auditoría. En un entorno multiusuario, es posible que el administrador quiera ver el historial de los usuarios que actualizan registros en una tabla en particular para auditorías.

Creará un activador que registre la actividad de actualización de la tabla sales. Nuestra tabla audit_log contendrá información sobre los usuarios de MySQL que actualicen la tabla sales, la date de la actualización y los valores sales_amount de new y old.

Para crear el activador, ejecute los siguientes comandos SQL:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

Se ingresa un nuevo registro en la tabla audit_log. Utilice la palabra clave NEW para obtener el valor de sales_id y el nuevo sales_amount. Además, utilice la palabra clave OLD para obtener el sales_amount anterior, ya que desea registrar ambos montos para auditoría.

Con el comando SELECT USER() se obtiene el usuario actual que realiza la operación y con la instrucción NOW() se obtiene el valor de la fecha y hora actuales del servidor de MySQL.

Si ahora un usuario intenta actualizar el valor de cualquier registro en la tabla sales, el activador log_sales_updates insertará un nuevo registro en la tabla audit_log.

Crearemos un nuevo registro de ventas con un sales_id aleatorio de 5 e intentaremos actualizarlo. Primero, inserte el registro de ventas con lo siguiente:

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

Output
Query OK, 1 row affected (0.00 sec)

A continuación, actualice el registro:

Update sales set sales_amount='9000' where sales_id='5';

Verá el siguiente resultado:

Output
Rows matched: 1  Changed: 1  Warnings: 0

Ahora ejecute el siguiente comando para verificar si con el activador AFTER UPDATE se pudo introducir un nuevo registro en la tabla audit_log:

Select * from audit_log;

En el activador se registró la actualización. En el resultado se muestran el sales_amount y el new amount anteriores registrados con el usuario que actualizó los registros:

Output
+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

También dispone de la fecha y hora en las que se realizó la actualización, que son valiosas para auditorías.

A continuación, usará el activador DELETE para aplicar la integridad referencial a nivel de base de datos.

Paso 6: Crear un activador “Before Delete”

Los activadores BEFORE DELETE se invocan antes de que se ejecute una instrucción DELETE en una tabla. Estos tipos de activadores normalmente se utilizan para implementar la integridad referencial en diferentes tablas relacionadas. Por ejemplo, cada registro de la tabla sales se relaciona a un customer_id de la tabla customers. Si un usuario de la base de datos eliminara un registro de la tabla customers que tiene un registro relacionado en la tabla sales, usted no tendría manera de conocer al cliente asociado a ese registro.

A fin de evitar esto, puede crear un activador BEFORE DELETE para aplicar su lógica. Ejecute los siguientes comandos SQL uno por uno:

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The customer has a related sales record.';
END IF//
DELIMITER ;

Ahora, intente eliminar un cliente que tenga un registro de ventas relacionado:

Delete from customers where customer_id='2';

Como resultado, obtendrá lo siguiente:

Output
ERROR 1644 (45000): The customer has a related sales record.

Con el activador BEFORE DELETE se puede evitar la eliminación accidental de información relacionada en una base de datos.

Sin embargo, en algunas situaciones, es posible que quiera eliminar todos los registros asociados con un registro específico de las diferentes tablas relacionadas. En este caso, usaría el activador AFTER DELETE, que probará en el siguiente paso.

Paso 7: Crear un activador “After Delete”

Los activadores AFTER DELETE funcionan una vez que se elimina correctamente un registro. Un ejemplo de cómo puede usar un activador AFTER DELETE es una situación en la cual el nivel de descuento que recibe un cliente en particular se determina mediante el número de ventas realizadas durante un período definido. Si alguno de los registros del cliente se eliminan de la tabla sales, el nivel de descuento de este deberá reducirse.

Otro uso del activador AFTER DELETE consiste en eliminar información relacionada de otra tabla una vez que se elimina un registro de una tabla básica. Por ejemplo, establecerá un activador con el que se elimine el registro del cliente si los registros de ventas con los customer_id relacionados se eliminan de la tabla sales. Ejecute el siguiente comando para crear su activador:

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

A continuación, ejecute lo siguiente para eliminar todos los registros de ventas asociados con un customer_id de 2:

Delete from sales where customer_id='2';

Output
Query OK, 1 row affected (0.00 sec)

Ahora compruebe si hay registros para el cliente en la tabla sales:

Select * from customers where customer_id='2';

Obtendrá un resultado Empty Set, ya que el activador eliminó el registro del cliente asociado con el customer_id de 2:

Output
Empty set (0.00 sec)

Con esto, habrá usado cada una de las diferentes formas de activadores para realizar funciones específicas. A continuación, verá la manera de eliminar un activador de la base de datos si ya no lo necesita.

Paso 8: Eliminar activadores

Con un método similar al que se emplea para cualquier otro objeto de base de datos, puede eliminar los activadores usando el comando DROP. A continuación, se muestra la sintaxis para eliminar un activador:

Drop trigger [TRIGGER NAME];

Por ejemplo, para eliminar el último activador AFTER DELETE que creó, ejecute el siguiente comando:

Drop trigger delete_related_info;

Output
Query OK, 0 rows affected (0.00 sec)

La necesidad de eliminar los activadores surge cuando desea recrear su estructura. En tal caso, puede eliminar el activador y redefinir uno nuevo con los diferentes comandos de activación.

Conclusión

A través de este tutorial, creó, utilizó y eliminó los diferentes tipos de activadores de una base de datos de MySQL. Usando un ejemplo de base de datos relacionada con los clientes, implementó activadores para diferentes casos de uso, como la validación de datos, la aplicación de lógica de negocios, el registro de auditorías y la aplicación de integridad referencial.

Para obtener más información sobre el uso de su base de datos de MySQL, consulte lo siguiente:

• Cómo optimizar MySQL con caché de consultas en Ubuntu 18.04
• Cómo implementar la paginación en MySQL con PHP en Ubuntu 18.04
• Cómo resolver problemas en MySQL