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

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

Introducción

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

Los activadores tienen varias ventajas. Por ejemplo, se pueden utilizar para generar el valor de una columna derivada durante una INSERTdeclaración. Otro caso de uso es la aplicación de la integridad referencial, donde se puede utilizar un activador para guardar un registro en varias tablas relacionadas. Otros beneficios incluyen el registro de las acciones del usuario en las tablas de auditoría, así como la copia en vivo de datos en diferentes esquemas de base de datos con fines de redundancia para evitar un único punto de falla.

También puede utilizar activadores para mantener las reglas de validación en el nivel de la base de datos. Esto ayuda a compartir la fuente de datos entre varias aplicaciones sin interrumpir la lógica empresarial. Esto reduce en gran medida los viajes de ida y vuelta al servidor de la base de datos, lo que mejora el tiempo de respuesta de sus aplicaciones. Dado que el servidor de la base de datos ejecuta activadores, estos pueden aprovechar los recursos mejorados del servidor, como la RAM y la CPU.

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

Prerrequisitos

Antes de comenzar, asegúrese de tener lo siguiente:

• Un servidor Ubuntu 18.04 configurado siguiendo la configuración inicial del servidor con Ubuntu 18.04, incluido un usuario sudo que no sea root.
• Una base de datos MySQL ejecutándose en su servidor siguiendo estos pasos: Cómo instalar MySQL en Ubuntu 18.04
• Credenciales de cuenta de usuario raíz para su base de datos MySQL.

Paso 1: creación de una base de datos de muestra

En este paso, creará una base de datos de clientes de muestra con varias tablas para demostrar cómo funcionan los activadores de MySQL.

Para comprender más sobre las consultas MySQL, lea nuestra Introducción a las consultas en MySQL.

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

mysql -u root -p

Ingrese su contraseña de root de MySQL cuando se le solicite y presione ENTERpara continuar. Cuando vea el mysqlmensaje, ejecute el siguiente comando para crear una test_dbbase de datos:

Create database test_db;

OutputQuery OK, 1 row affected (0.00 sec)

A continuación, cambie a test_dbcon:

Use test_db;

OutputDatabase changed

Comenzará creando una customerstabla. Esta tabla contendrá los registros de los clientes customer_id, incluidos customer_name, y level. Habrá dos niveles de cliente: BASICy VIP.

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

OutputQuery OK, 0 rows affected (0.01 sec)

Ahora, agregue algunos registros a la customerstabla. Para ello, 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 INSERTcomandos:

OutputQuery OK, 1 row affected (0.01 sec)

Para asegurarse de que los registros de muestra se insertaron correctamente, ejecute el SELECTcomando:

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 almacenar información relacionada con la customerscuenta. La tabla tendrá campos customer_idy 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 salestabla. Esta tabla contendrá los datos de ventas relacionados con los diferentes clientes a través de la customer_idcolumna:

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

OutputQuery OK, 0 rows affected (0.01 sec)

Agregará datos de muestra a los salesdatos en los próximos pasos mientras prueba los activadores. A continuación, cree una audit_logtabla para registrar las actualizaciones realizadas en la salestabla cuando implemente el AFTER UPDATEactivador 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;

OutputQuery OK, 0 rows affected (0.02 sec)

Con la test_dbbase de datos y las cuatro tablas en su lugar, ahora pasará a trabajar con los diferentes activadores MySQL en su base de datos.

Paso 2: creación de un disparador antes de insertar

En este paso, examinará la sintaxis de un disparador MySQL antes de aplicar esta lógica para crear un BEFORE INSERTdisparador que valide el sales_amountcampo cuando se insertan datos en la salestabla.

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

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

La estructura del disparador incluye:

DELIMITER //:El delimitador predeterminado de MySQL es ;—es necesario cambiarlo por otro para que MySQL trate las siguientes líneas como un solo comando hasta que llegue a su delimitador personalizado. En este ejemplo, el delimitador se cambia a //y luego ;se redefine al final.

[TRIGGER_NAME]:Un disparador debe tener un nombre y aquí es donde se incluye el valor.

[TRIGGER TIME]:Un disparador puede activarse en distintos momentos. MySQL le permite definir si el disparador se iniciará antes o después de una operación de base de datos.

[TRIGGER EVENT]: Los activadores solo se invocan mediante operaciones INSERT, UPDATEy DELETE. Puede utilizar cualquier valor aquí según lo que desee lograr.

[TABLE]:Cualquier disparador que cree en su base de datos MySQL debe estar asociado con una tabla.

FOR EACH ROW:Esta declaración le dice a MySQL que ejecute el código de activación para cada fila que el activador afecta.

[TRIGGER BODY]:El código que se ejecuta cuando se invoca el disparador se denomina cuerpo del disparador. Puede ser una sola instrucción SQL o varios comandos. Tenga en cuenta que si está ejecutando varias instrucciones SQL en el cuerpo del disparador, debe encapsularlas en un BEGIN...ENDbloque.

Nota: Al crear el cuerpo del disparador, puede utilizar las palabras clave OLDy NEWpara acceder a los valores de columna antiguos y nuevos ingresados ​​durante una operación INSERT, UPDATEy DELETE. En un DELETEdisparador, solo OLDse puede utilizar la palabra clave (que utilizará en el paso 4).

Ahora creará su primer BEFORE INSERTdisparador. Este disparador se asociará con la salestabla y se invocará antes de que se inserte un registro para validar el sales_amount. La función del disparador es verificar si el sales_amountque se inserta en la tabla de ventas es mayor que 10000y generar un error si se evalúa como verdadero.

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

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

Utiliza la IF...THEN...END IFinstrucción para evaluar si la cantidad que se proporciona durante la INSERTinstrucción está dentro de su rango. El disparador puede extraer el nuevo sales_amountvalor que se proporciona mediante el uso de la NEWpalabra clave.

Para generar un mensaje de error genérico, utilice las siguientes líneas para 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_amountof 11000en la salestabla para comprobar si el disparador detendrá la operación:

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

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

Este error muestra que el código de activación está funcionando como se esperaba.

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

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

Dado que el valor está dentro del rango recomendado, verá el siguiente resultado:

OutputQuery OK, 1 row affected (0.01 sec)

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

Select * from sales;

La salida confirma que los datos están en la tabla:

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

En este paso, ha probado los activadores para validar los datos antes de insertarlos en una base de datos.

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

Paso 3: creación de un disparador posterior a la inserción

AFTER INSERTLos activadores se ejecutan cuando se insertan registros correctamente en una tabla. Esta funcionalidad se puede utilizar para ejecutar automáticamente otras lógicas relacionadas con el negocio. Por ejemplo, en una aplicación bancaria, un AFTER INSERTactivador puede cerrar una cuenta de préstamo cuando un cliente termina de pagar el préstamo. El activador puede monitorear todos los pagos insertados en una tabla de transacciones y cerrar el préstamo automáticamente una vez que el saldo del préstamo sea cero.

En este paso, trabajará con su customer_statustabla utilizando un AFTER INSERTdisparador para ingresar registros de clientes relacionados.

Para crear el AFTER INSERTdisparador, ingrese 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 ;

OutputQuery OK, 0 rows affected (0.00 sec)

Aquí le indica a MySQL que guarde otro registro en la customer_statustabla una vez que se inserte un nuevo registro de cliente en la customerstabla.

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

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

OutputQuery OK, 1 row affected (0.01 sec)

Dado que el registro se insertó correctamente, verifique que se haya insertado un nuevo registro de estado en la customer_statustabla:

Select * from customer_status;

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

La salida confirma que el disparador se ejecutó correctamente.

El AFTER INSERTdisparador es útil para monitorear 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 de la cuenta.

En los siguientes pasos trabajarás con UPDATEactivadores.

Paso 4: creación de un disparador previo a la actualización

Un BEFORE UPDATEdisparador es similar a un BEFORE INSERTactivador: la diferencia está en el momento en que se invocan. Puedes usar el BEFORE UPDATEdisparador para comprobar una lógica empresarial antes de que se actualice un registro. Para probar esto, usarás la customerstabla en la que ya has insertado algunos datos.

Tiene dos niveles para sus clientes en la base de datos. En este ejemplo, una vez que una cuenta de cliente se actualiza al VIPnivel, la cuenta no se puede degradar al BASICnivel anterior. Para aplicar dicha regla, creará un BEFORE UPDATEdisparador que se ejecutará antes de la UPDATEdeclaración, como se muestra a continuación. Si un usuario de la base de datos intenta degradar a un cliente al BASICnivel anterior VIP, se activará una excepción definida por el usuario.

Ingrese los siguientes comandos SQL uno por uno para crear el BEFORE UPDATEdisparador:

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 OLDpalabra clave para capturar el nivel que el usuario proporciona al ejecutar el UPDATEcomando. Nuevamente, utilice la IF...THEN...END IFdeclaración para indicarle al usuario una declaración de error genérica.

A continuación, ejecute el siguiente comando SQL que intenta degradar una cuenta de cliente asociada con el customer_idde 3:

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

Verá el siguiente resultado que proporciona SET MESSAGE_TEXT:

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

Si ejecuta el mismo comando para un BASICcliente de nivel e intenta actualizar la cuenta al VIPnivel, el comando se ejecutará correctamente:

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

OutputRows matched: 1  Changed: 1  Warnings: 0

Ha utilizado el BEFORE UPDATEdisparador para aplicar una regla de negocio. Ahora, pasará a utilizar un AFTER UPDATEdisparador para el registro de auditoría.

Paso 5: Creación de un disparador posterior a la actualización

Se invoca un AFTER UPDATEdisparador una vez que se actualiza correctamente un registro de base de datos. Este comportamiento hace que el disparador sea adecuado para el registro de auditoría. En un entorno multiusuario, el administrador puede querer ver un historial de usuarios que actualizan registros en una tabla en particular para fines de auditoría.

Creará un disparador que registre la actividad de actualización de la salestabla. Nuestra audit_logtabla contendrá información sobre los usuarios de MySQL que actualizan la salestabla, el momento datede la actualización y los valores newy .old sales_amount

Para crear el disparador, 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 ;

Inserta un nuevo registro en la audit_logtabla. Utiliza la NEWpalabra clave para recuperar el valor de sales_idy el nuevo sales_amount. También utiliza la OLDpalabra clave para recuperar el anterior sales_amount, ya que desea registrar ambos montos para fines de auditoría.

El comando SELECT USER()recupera el usuario actual que realiza la operación y la NOW()declaración recupera el valor de la fecha y hora actuales del servidor MySQL.

Ahora, si un usuario intenta actualizar el valor de cualquier registro de la salestabla, el log_sales_updatesdisparador insertará un nuevo registro en la audit_logtabla.

Vamos a crear un nuevo registro de ventas con un número aleatorio sales_idde 5e intentar actualizarlo. Primero, inserte el registro de ventas con:

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

OutputQuery OK, 1 row affected (0.00 sec)

A continuación, actualice el registro:

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

Verás el siguiente resultado:

OutputRows matched: 1  Changed: 1  Warnings: 0

Ahora ejecute el siguiente comando para verificar si el AFTER UPDATEdisparador pudo registrar un nuevo registro en la audit_logtabla:

Select * from audit_log;

El disparador registró la actualización. Su salida muestra los registros anteriores sales_amounty new amountregistrados 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 tiene la fecha y la hora en que se realizó la actualización, que son valiosas para fines de auditoría.

A continuación, utilizará el DELETEdisparador para imponer la integridad de la referencia a nivel de la base de datos.

Paso 6: creación de un disparador antes de eliminar

BEFORE DELETELos activadores se invocan antes de que se ejecute una DELETEinstrucción en una tabla. Este tipo de activadores se utilizan normalmente para aplicar la integridad referencial en diferentes tablas relacionadas. Por ejemplo, cada registro de la salestabla se relaciona con un registro customer_idde la customerstabla. Si un usuario de la base de datos elimina un registro de la customerstabla que tiene un registro relacionado en la salestabla, no tendría forma de saber el cliente asociado con ese registro.

Para evitarlo, puede crear un BEFORE DELETEdisparador 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 recibirás el siguiente resultado:

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

El BEFORE DELETEdisparador puede evitar la eliminación accidental de información relacionada en una base de datos.

Sin embargo, en algunas situaciones, es posible que desee eliminar todos los registros asociados con un registro en particular de las diferentes tablas relacionadas. En esta situación, deberá utilizar el AFTER DELETEdisparador, que probará en el siguiente paso.

Paso 7: creación de un disparador After Delete

AFTER DELETELos activadores se activan una vez que se ha eliminado un registro correctamente. Un ejemplo de cómo se puede utilizar un AFTER DELETEactivador es una situación en la que el nivel de descuento que recibe un cliente en particular está determinado por la cantidad de ventas realizadas durante un período definido. Si se eliminan algunos de los registros del cliente de la salestabla, sería necesario reducir el nivel de descuento del cliente.

Otro uso del AFTER DELETEdisparador es eliminar información relacionada de otra tabla una vez que se elimina un registro de una tabla base. Por ejemplo, puede configurar un disparador que elimine el registro del cliente si los registros de ventas relacionados customer_idse eliminan de la salestabla. Ejecute el siguiente comando para crear el disparador:

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_idde 2:

Delete from sales where customer_id='2';

OutputQuery OK, 1 row affected (0.00 sec)

Ahora verifique si hay registros para el cliente en la salestabla:

Select * from customers where customer_id='2';

Recibirá un Empty Setresultado ya que el registro del cliente asociado con el customer_idde 2fue eliminado por el disparador:

OutputEmpty set (0.00 sec)

Ya ha utilizado cada una de las diferentes formas de activadores para realizar funciones específicas. A continuación, verá cómo puede eliminar un activador de la base de datos si ya no lo necesita.

Paso 8: eliminar activadores

De manera similar a cualquier otro objeto de base de datos, puede eliminar activadores mediante el DROPcomando. La siguiente es la sintaxis para eliminar un activador:

Drop trigger [TRIGGER NAME];

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

Drop trigger delete_related_info;

OutputQuery OK, 0 rows affected (0.00 sec)

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

Conclusión

En este tutorial, ha creado, utilizado y eliminado los distintos tipos de activadores de una base de datos MySQL. Con una base de datos relacionada con el cliente como ejemplo, ha implementado activadores para distintos casos de uso, como validación de datos, aplicación de lógica empresarial, registro de auditoría y aplicación de integridad referencial.

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

• Cómo optimizar MySQL con Query Cache en Ubuntu 18.04
• Cómo implementar paginación en MySQL con PHP en Ubuntu 18.04
• Cómo solucionar problemas en MySQL