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
, DELETE
o 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 INSERT
declaració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 ENTER
para continuar. Cuando vea el mysql
mensaje, ejecute el siguiente comando para crear una test_db
base de datos:
- Create database test_db;
OutputQuery OK, 1 row affected (0.00 sec)
A continuación, cambie a test_db
con:
- Use test_db;
OutputDatabase changed
Comenzará creando una customers
tabla. Esta tabla contendrá los registros de los clientes customer_id
, incluidos 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;
OutputQuery OK, 0 rows affected (0.01 sec)
Ahora, agregue algunos registros a la customers
tabla. 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 INSERT
comandos:
OutputQuery OK, 1 row affected (0.01 sec)
Para asegurarse de que los registros de muestra se insertaron correctamente, ejecute el SELECT
comando:
- 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 customers
cuenta. La tabla tendrá campos customer_id
y 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 sales
tabla. Esta tabla contendrá los datos de ventas relacionados con los diferentes clientes a través de la customer_id
columna:
- 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 sales
datos en los próximos pasos mientras prueba los activadores. A continuación, cree una audit_log
tabla para registrar las actualizaciones realizadas en la sales
tabla cuando implemente el AFTER UPDATE
activador 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_db
base 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 INSERT
disparador que valide el sales_amount
campo cuando se insertan datos en la sales
tabla.
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
, UPDATE
y 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...END
bloque.
Nota: Al crear el cuerpo del disparador, puede utilizar las palabras clave OLD
y NEW
para acceder a los valores de columna antiguos y nuevos ingresados durante una operación INSERT
, UPDATE
y DELETE
. En un DELETE
disparador, solo OLD
se puede utilizar la palabra clave (que utilizará en el paso 4).
Ahora creará su primer BEFORE INSERT
disparador. Este disparador se asociará con la sales
tabla 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_amount
que se inserta en la tabla de ventas es mayor que 10000
y 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 IF
instrucción para evaluar si la cantidad que se proporciona durante la INSERT
instrucción está dentro de su rango. El disparador puede extraer el nuevo sales_amount
valor que se proporciona mediante el uso de la NEW
palabra 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_amount
of 11000
en la sales
tabla 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 7500
para 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 INSERT
disparador para guardar información relacionada en diferentes tablas.
Paso 3: creación de un disparador posterior a la inserción
AFTER INSERT
Los 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 INSERT
activador 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_status
tabla utilizando un AFTER INSERT
disparador para ingresar registros de clientes relacionados.
Para crear el AFTER INSERT
disparador, 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_status
tabla una vez que se inserte un nuevo registro de cliente en la customers
tabla.
Ahora, inserte un nuevo registro en la customers
tabla 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_status
tabla:
- 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 INSERT
disparador 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 UPDATE
activadores.
Paso 4: creación de un disparador previo a la actualización
Un BEFORE UPDATE
disparador es similar a un BEFORE INSERT
activador: la diferencia está en el momento en que se invocan. Puedes usar el BEFORE UPDATE
disparador para comprobar una lógica empresarial antes de que se actualice un registro. Para probar esto, usarás la customers
tabla 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 VIP
nivel, la cuenta no se puede degradar al BASIC
nivel anterior. Para aplicar dicha regla, creará un BEFORE UPDATE
disparador que se ejecutará antes de la UPDATE
declaración, como se muestra a continuación. Si un usuario de la base de datos intenta degradar a un cliente al BASIC
nivel anterior VIP
, se activará una excepción definida por el usuario.
Ingrese los siguientes comandos SQL uno por uno para crear el BEFORE UPDATE
disparador:
- 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 OLD
palabra clave para capturar el nivel que el usuario proporciona al ejecutar el UPDATE
comando. Nuevamente, utilice la IF...THEN...END IF
declaració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_id
de 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 BASIC
cliente de nivel e intenta actualizar la cuenta al VIP
nivel, 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 UPDATE
disparador para aplicar una regla de negocio. Ahora, pasará a utilizar un AFTER UPDATE
disparador para el registro de auditoría.
Paso 5: Creación de un disparador posterior a la actualización
Se invoca un AFTER UPDATE
disparador 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 sales
tabla. Nuestra audit_log
tabla contendrá información sobre los usuarios de MySQL que actualizan la sales
tabla, el momento date
de la actualización y los valores new
y .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_log
tabla. Utiliza la NEW
palabra clave para recuperar el valor de sales_id
y el nuevo sales_amount
. También utiliza la OLD
palabra 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 sales
tabla, el log_sales_updates
disparador insertará un nuevo registro en la audit_log
tabla.
Vamos a crear un nuevo registro de ventas con un número aleatorio sales_id
de 5
e 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 UPDATE
disparador pudo registrar un nuevo registro en la audit_log
tabla:
- Select * from audit_log;
El disparador registró la actualización. Su salida muestra los registros anteriores sales_amount
y new amount
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 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 DELETE
disparador 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 DELETE
Los activadores se invocan antes de que se ejecute una DELETE
instrucció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 sales
tabla se relaciona con un registro customer_id
de la customers
tabla. Si un usuario de la base de datos elimina un registro de la customers
tabla que tiene un registro relacionado en la sales
tabla, no tendría forma de saber el cliente asociado con ese registro.
Para evitarlo, puede crear un BEFORE DELETE
disparador 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 DELETE
disparador 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 DELETE
disparador, que probará en el siguiente paso.
Paso 7: creación de un disparador After Delete
AFTER DELETE
Los activadores se activan una vez que se ha eliminado un registro correctamente. Un ejemplo de cómo se puede utilizar un AFTER DELETE
activador 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 sales
tabla, sería necesario reducir el nivel de descuento del cliente.
Otro uso del AFTER DELETE
disparador 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_id
se eliminan de la sales
tabla. 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_id
de 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 sales
tabla:
- Select * from customers where customer_id='2';
Recibirá un Empty Set
resultado ya que el registro del cliente asociado con el customer_id
de 2
fue 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 DROP
comando. La siguiente es la sintaxis para eliminar un activador:
Drop trigger [TRIGGER NAME];
Por ejemplo, para eliminar el último AFTER DELETE
disparador 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
Deja una respuesta