Cómo usar disparadores en MySQL

El autor seleccionó el programa Write for DOnations.

Introducción

Cuando se trabaja con bases de datos relacionales y UPDATE.

Sin embargo, las bases de datos SQL también pueden recibir instrucciones para realizar acciones predefinidas automáticamente cada vez que ocurre un evento específico a través de disparadores. Por ejemplo, puede usar disparadores para mantener el registro de seguimiento de auditoría de todas las declaraciones DELETE o actualizar automáticamente los resúmenes estadísticos agregados cada vez que las filas se actualizan o se agregan a la tabla.

En este tutorial, utilizará diferentes activadores de SQL para realizar automáticamente acciones en las que se insertan, actualizan o eliminan filas.

requisitos previos

Para seguir esta guía, necesitará una computadora que ejecute un sistema de administración de bases de datos relacionales (RDBMS) basado en SQL. Las instrucciones y los ejemplos de esta guía se validaron utilizando el siguiente entorno:

• Un servidor que ejecuta Ubuntu 20.04, con un usuario no raíz con privilegios administrativos y un firewall configurado con UFW, como se describe en nuestra guía de configuración inicial del servidor para Ubuntu 20.04.
• MySQL instalado y protegido en el servidor, como se describe en el Paso 3.
• Familiaridad básica con la ejecución de consultas SELECT, INSERT, UPDATE y DELETE para manipular datos en la base de datos como se describe en nuestras guías Cómo eliminar datos en SQL.
• Familiaridad básica con el uso de consultas anidadas como se describe en nuestra guía Cómo usar consultas anidadas en SQL.
• Familiaridad básica con el uso de funciones matemáticas agregadas como se describe en nuestra guía Cómo usar expresiones matemáticas y funciones agregadas en SQL.

Nota: muchos RDBMS utilizan su propia implementación de SQL. Aunque los activadores se mencionan como parte del estándar SQL, el estándar no impone su sintaxis ni la forma estricta de implementarlos. Como resultado, su implementación difiere entre diferentes bases de datos. Los comandos descritos en este tutorial utilizan la sintaxis de la base de datos MySQL y es posible que no funcionen en otros motores de base de datos.

También necesitará una base de datos con algunas tablas cargadas con datos de muestra para que pueda practicar el uso de funciones. Le recomendamos que consulte la siguiente sección Conexión a MySQL y configuración de una base de datos de muestra para obtener detalles sobre cómo conectarse a un servidor MySQL y crear la base de datos de prueba utilizada en los ejemplos de esta guía.

Conexión a MySQL y configuración de una base de datos de muestra

En esta sección, se conectará a un servidor MySQL y creará una base de datos de muestra para que pueda seguir los ejemplos de las siguientes secciones.

Para esta guía, utilizará una colección imaginaria de coleccionables. Almacenará detalles sobre los coleccionables que posee actualmente, mantendrá su valor total fácilmente disponible y se asegurará de que la eliminación de un coleccionable siempre deje un rastro.

Si su sistema de base de datos SQL se ejecuta en un servidor remoto, SSH en su servidor desde su máquina local:

ssh sammy@your_server_ip

Luego abra el indicador del servidor MySQL, reemplazando sammy con el nombre de su cuenta de usuario de MySQL:

mysql -u sammy -p

Crea una base de datos llamada coleccionables:

CREATE DATABASE collectibles;

Si la base de datos se creó correctamente, recibirá un resultado como este:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos coleccionables, ejecute la siguiente instrucción USE:

USE collectibles;

Recibirá el siguiente resultado:

Output
Database changed

Después de seleccionar la base de datos, puede crear tablas de muestra dentro de ella. La tabla coleccionables contendrá datos simplificados sobre los coleccionables en la base de datos. Contendrá las siguientes columnas:

• name: esta columna contiene el nombre de cada coleccionable, expresado con el tipo de datos varchar con un máximo de 50 caracteres.
• valor: esta columna almacena el valor de mercado del coleccionable utilizando el tipo de datos decimal con un máximo de 5 valores antes del punto decimal y 2 valores después de él.

Cree la tabla de muestra con el siguiente comando:

CREATE TABLE collectibles (
    name varchar(50),
    value decimal(5, 2)
);

Si se imprime el siguiente resultado, la tabla se ha creado:

Output
Query OK, 0 rows affected (0.00 sec)

La siguiente tabla se llamará collectibles_stats y se utilizará para realizar un seguimiento del valor acumulado de todos los coleccionables de la colección. Contendrá una sola fila de datos con las siguientes columnas:

• recuento: esta columna contiene el número de coleccionables en propiedad, expresado mediante el tipo de datos int.
• valor: esta columna almacena el valor acumulado de todos los coleccionables usando el tipo de datos decimal con un máximo de 5 valores antes del punto decimal y 2 valores después de él.

Cree la tabla de muestra con el siguiente comando:

CREATE TABLE collectibles_stats (
    count int,
    value decimal(5, 2)
);

Si se imprime el siguiente resultado, la tabla se ha creado:

Output
Query OK, 0 rows affected (0.00 sec)

La tercera y última tabla se llamará collectibles_archive, que realizará un seguimiento de todos los coleccionables que se han eliminado de la colección para garantizar que nunca desaparezcan. Contendrá datos similares a la tabla coleccionables, aumentados con la fecha de eliminación. Utiliza las siguientes columnas:

• name: esta columna contiene el nombre de cada coleccionable eliminado, expresado con el tipo de datos varchar con un máximo de 50 caracteres.< /li>
• valor: esta columna almacena el valor de mercado del coleccionable en el momento de la eliminación utilizando el tipo de datos decimal con un máximo de 5 valores antes el punto decimal y los valores 2 después de él.
• removed_on: esta columna almacena la fecha y hora de eliminación de cada coleccionable archivado usando el tipo de datos timestamp con el valor predeterminado de NOW(), es decir, la fecha actual cada vez que se inserta una nueva fila en esta tabla.

Cree la tabla de muestra con el siguiente comando:

CREATE TABLE collectibles_archive (
    name varchar(50),
    value decimal(5, 2),
    removed_on timestamp DEFAULT CURRENT_TIMESTAMP
);

Si se imprime el siguiente resultado, la tabla se ha creado:

Output
Query OK, 0 rows affected (0.00 sec)

A continuación, cargue la tabla collectibles_stats con el estado inicial de la colección de coleccionables vacía ejecutando la siguiente operación INSERT INTO:

INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

La operación INSERT INTO agregará una sola fila a collectibles_stats con los valores calculados usando las funciones agregadas para contar todas las filas en la tabla collectibles y para sumar el valor de todos los coleccionables usando la columna value y la función SUM. El siguiente resultado indica que se ha agregado la fila:

Output
Query OK, 1 row affected (0.002 sec)
Records: 1  Duplicates: 0  Warnings: 0

Puede verificar eso ejecutando una instrucción SELECT en la tabla:

SELECT * FROM collectibles_stats;

Dado que todavía no hay coleccionables en la base de datos, el número inicial de artículos es 0 y el valor acumulado dice NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Con eso, está listo para seguir el resto de la guía y comenzar a usar disparadores en MySQL.

Comprender los desencadenantes

Los disparadores son declaraciones definidas para una tabla en particular que la base de datos ejecuta automáticamente cada vez que ocurre un evento específico en esa tabla. Los activadores se pueden utilizar para garantizar que algunas acciones se realizarán de forma coherente cada vez que se ejecute una declaración específica en una tabla, en lugar de que los usuarios de la base de datos tengan que recordar ejecutarlas manualmente.

Cada disparador asociado con una tabla se identifica con un nombre definido por el usuario y un par de condiciones que indican al motor de la base de datos cuándo ejecutar el disparador. Estos se pueden agrupar en dos clases separadas:

• Evento de base de datos: el activador se puede ejecutar cuando se ejecutan declaraciones INSERT, UPDATE o DELETE en una tabla.
• Tiempo del evento: además, los activadores se pueden ejecutar ANTES o DESPUÉS de la instrucción en cuestión.

La combinación de los dos grupos de condiciones produce un total de seis posibilidades de activación separadas que se ejecutan automáticamente cada vez que se cumple la condición conjunta. Los activadores que ocurren antes de que se ejecute la instrucción que cumple la condición son ANTES DE INSERTAR, ANTES DE ACTUALIZAR y ANTES DE ELIMINAR. Estos se pueden usar para manipular y validar datos antes de que se inserten o actualicen en la tabla, o para guardar los detalles de la fila eliminada con fines de auditoría o archivo.

Los activadores que ocurren después de que se ejecuta la instrucción que cumple la condición son AFTER INSERT, AFTER UPDATE y AFTER DELETE. Estos se pueden usar para actualizar los valores resumidos en una tabla separada según el estado final de la base de datos después de la declaración.

Para realizar acciones como validar y manipular los datos de entrada o archivar la fila eliminada, la base de datos permite acceder a los valores de los datos desde los disparadores. Para activadores INSERT, solo se pueden usar los datos recién insertados. Para activadores UPDATE, se puede acceder tanto a los datos originales como a los actualizados. Finalmente, con los activadores DELETE, solo los datos de la fila original están disponibles para su uso (ya que no hay datos nuevos a los que hacer referencia).

Los datos para usar dentro del cuerpo del activador se exponen bajo el registro OLD para los datos que se encuentran actualmente en la base de datos y el registro NEW para los datos que guardará la consulta. Puede hacer referencia a columnas individuales utilizando la sintaxis OLD.column_name y NEW.column_name.

El siguiente ejemplo muestra la sintaxis general de una instrucción SQL utilizada para crear un nuevo disparador:

CREATE TRIGGER trigger_name trigger_condition
ON table_name
FOR EACH ROW
trigger_actions;

Vamos a diseccionar la sintaxis en partes más pequeñas:

• CREATE TRIGGER es el nombre de la instrucción SQL utilizada para crear un nuevo activador en la base de datos.
• trigger_name es el nombre definido por el usuario del activador, que se utiliza para describir su función, de forma similar a cómo se utilizan los nombres de tablas y columnas para describir su significado.< /li>
• ON table_name le dice a la base de datos que el disparador debe monitorear los eventos que suceden en la tabla table_name.
• trigger_condition es una de las seis opciones posibles que definen cuándo debe ejecutarse el disparador, por ejemplo, ANTES DE INSERTAR.
• PARA CADA FILA le dice a la base de datos que el activador debe ejecutarse para cada fila afectada por el evento activador. Algunas bases de datos admiten patrones de ejecución adicionales además de PARA CADA FILA; sin embargo, en el caso de MySQL, la única opción es ejecutar las instrucciones desde el cuerpo del activador para cada fila afectada por la instrucción que provocó la ejecución del activador.
• trigger_actions es el cuerpo del disparador y define lo que sucede cuando se ejecuta el disparador. Por lo general, es una sola instrucción SQL válida. Es posible incluir varias declaraciones en el cuerpo del activador para realizar operaciones de datos complejas utilizando las palabras clave BEGIN y END para encerrar la lista de declaraciones en un bloque. Sin embargo, esto está fuera del alcance de este tutorial. Consulte la documentación oficial de los disparadores para obtener más información sobre la sintaxis utilizada para definir los disparadores.

En la siguiente sección, creará disparadores que manipulen datos antes de las operaciones INSERT y UPDATE.

Manipulación de datos con activadores ANTES DE INSERTAR y ANTES DE ACTUALIZAR

En esta sección, utilizará disparadores para manipular datos antes de que se ejecuten las instrucciones INSERT y UPDATE.

En este ejemplo, usará disparadores para asegurarse de que todos los coleccionables en la base de datos usen nombres en mayúsculas para mantener la coherencia. Sin usar activadores, debe recordar usar nombres coleccionables en mayúsculas para cada instrucción INSERT y UPDATE. Si lo olvida, la base de datos guardará los datos tal como están, lo que provocará posibles errores en el conjunto de datos.

Comenzará insertando un objeto coleccionable de ejemplo llamado modelo de nave espacial por valor de $12.50. El nombre del elemento se escribirá en minúsculas para ilustrar el problema. Ejecute la siguiente declaración:

INSERT INTO collectibles VALUES ('spaceship model', 12.50);

El siguiente mensaje confirma que se agregó el elemento:

Output
Query OK, 1 row affected (0.009 sec)

Puede verificar que la fila se insertó ejecutando la consulta SELECT:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

El artículo coleccionable se ha guardado tal cual, con el nombre escrito solo con letras minúsculas.

Para asegurarse de que todos los coleccionables futuros siempre se escribirán en mayúsculas, creará un activador ANTES DE INSERTAR. El uso de un activador que se ejecuta antes de que se ejecute la declaración de activación le permite manipular los datos que se pasarán a la base de datos antes de que suceda.

Ejecute la siguiente instrucción:

CREATE TRIGGER uppercase_before_insert BEFORE INSERT
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

Este comando crea un disparador llamado uppercase_before_insert que se ejecutará ANTES de todas las instrucciones INSERT en la tabla llamada collectibles.

La declaración en el activador SET NEW.name=UPPER(NEW.name) se ejecutará para cada fila insertada. El comando SQL SET asigna el valor del lado derecho al lado izquierdo. En este caso, NEW.name representa el valor de la columna name que guardará la declaración de inserción. Al aplicar la función UPPER en el nombre del coleccionable y asignarlo nuevamente al valor de la columna, está convirtiendo el valor que se guardará en la base de datos.

Nota: Al ejecutar el comando CREATE TRIGGER, es posible que encuentre un mensaje de error similar a ERROR 1419 (HY000): No tiene el privilegio SUPER y el registro binario está habilitado (es posible que * * desea utilizar la variable menos segura log_bin_trust_function_creators).

A partir de MySQL 8, el motor de la base de datos MySQL tiene habilitado el registro binario de forma predeterminada, a menos que la configuración de la instalación local lo anule. El registro binario realiza un seguimiento de todas las declaraciones SQL que modifican el contenido de la base de datos en forma de eventos guardados que describen las modificaciones. Estos registros se utilizan en la replicación de la base de datos para mantener las réplicas de la base de datos sincronizadas y durante la recuperación de datos en un momento dado.

Con el registro binario habilitado, MySQL no permite la creación de disparadores y procedimientos almacenados como medida de precaución para garantizar la seguridad e integridad de los datos en entornos replicados. Comprender cómo los disparadores y los procedimientos almacenados pueden afectar la replicación está fuera del alcance de esta guía.

Sin embargo, en un entorno local y con fines de aprendizaje, puede anular de forma segura la forma en que MySQL protege contra la creación de disparadores. La configuración anulada no se conserva y volverá al valor original cuando se reinicie el servidor MySQL.

Para anular la configuración predeterminada para el registro binario, inicie sesión en MySQL como raíz y ejecute el siguiente comando:

SET GLOBAL log_bin_trust_function_creators = 1;

La configuración log_bin_trust_function_creators controla si se puede confiar en que los usuarios que crean disparadores y funciones almacenadas no creen disparadores que provoquen que se escriban eventos no seguros en el registro binario. De forma predeterminada, el valor de la configuración es 0, lo que permite que solo los superusuarios creen disparadores en un entorno con el registro binario habilitado. Al cambiar el valor a 1, se confiará en cualquier usuario que emita declaraciones CREATE TRIGGER para comprender las implicaciones.

Después de actualizar la configuración, cierre sesión como raíz, vuelva a iniciar sesión como usuario y vuelva a ejecutar la instrucción CREATE TRIGGER.

Para obtener más información sobre el registro binario y la replicación en MySQL y cómo se relaciona con los activadores, le recomendamos que consulte la documentación oficial de MySQL: Cómo configurar la replicación en MySQL.

Antes de usar disparadores en un entorno de producción con replicación en su lugar o requisitos estrictos de recuperación en un momento dado, asegúrese de haber sopesado su impacto en la consistencia del registro binario.

Nota: Dependiendo de sus permisos de usuario de MySQL, puede recibir un error al ejecutar el comando CREATE TRIGGER: ERROR 1142 (42000): comando TRIGGER denegado al usuario usuario@host para coleccionables de mesa. Para otorgar permisos de TRIGGER a su usuario, inicie sesión en MySQL como root y ejecute los siguientes comandos, reemplazando el nombre de usuario y host de MySQL según sea necesario:

GRANT TRIGGER on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

Después de actualizar los permisos de usuario, cierre sesión como root, vuelva a iniciar sesión como usuario y vuelva a ejecutar la sentencia CREATE TRIGGER.

MySQL imprimirá el siguiente mensaje para confirmar que el activador se creó correctamente:

Output
Query OK, 1 row affected (0.009 sec)

Ahora intente insertar un nuevo coleccionable, nuevamente usando un argumento en minúsculas para la consulta INSERT:

INSERT INTO collectibles VALUES ('aircraft model', 10.00);

Y una vez más, comprueba las filas resultantes en la tabla collectibles:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Esta vez, sin embargo, la nueva entrada dice AIRCRAFT MODEL con todas las letras en mayúsculas, diferente a la entrada que intentó insertar. El activador se ejecutó en segundo plano y convirtió la letra mayúscula antes de que la fila se guardara en la base de datos.

Todas las filas nuevas ahora están protegidas por el activador para garantizar que los nombres se guarden en mayúsculas. Sin embargo, aún es posible guardar datos sin restricciones usando declaraciones UPDATE. Para proteger las sentencias UPDATE con el mismo efecto, cree otro disparador:

CREATE TRIGGER uppercase_before_update BEFORE UPDATE
ON collectibles
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

La diferencia entre los dos activadores está en los criterios de activación. Esta vez, es ANTES DE LA ACTUALIZACIÓN, lo que significa que el disparador se ejecutará cada vez que se emita una instrucción UPDATE en la tabla, lo que afectará las filas existentes en cada actualización. , además de las nuevas filas cubiertas por el activador anterior.

MySQL generará una confirmación de que el activador se creó con éxito:

Output
Query OK, 0 row affected (0.009 sec)

Para verificar el comportamiento del nuevo activador, intenta actualizar el valor del precio del modelo de nave espacial:

UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

La cláusula WHERE filtra la fila que se actualizará por nombre, y la cláusula SET cambia el valor a 15.00.

Recibirá el siguiente resultado, confirmando que la declaración cambió una sola fila:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Comprueba las filas resultantes en la tabla coleccionables:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| AIRCRAFT MODEL  | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Ahora, además de la actualización del precio a 15.00 por la declaración ejecutada, el nombre ahora dice MODELO DE NAVE. Cuando ejecutó la instrucción UPDATE, se ejecutó el disparador, lo que afectó los valores en la fila actualizada. La columna de nombre se convirtió a mayúsculas antes de guardar.

En esta sección, creó dos activadores que funcionan antes de las consultas INSERT y UPDATE para conformar los datos antes de guardarlos en la base de datos. En la siguiente sección, usará activadores ANTES DE ELIMINAR para copiar filas eliminadas en una tabla separada para archivar.

Uso de activadores ANTES DE ELIMINAR para ejecutar acciones antes de eliminar filas

Incluso si ya no posee un elemento, es posible que desee dejar una entrada sobre la eliminación en una tabla separada. Al comienzo de este tutorial, creó una segunda tabla llamada collectibles_archive para realizar un seguimiento de todos los coleccionables que se han eliminado de la colección. En esta sección, archivará las entradas eliminadas con un activador que se ejecutará antes de las declaraciones DELETE.

Verifique si la tabla de archivo está completamente vacía ejecutando la siguiente declaración:

SELECT * FROM collectibles_archive;

El siguiente resultado se imprimirá en la pantalla, confirmando que la tabla collectibles_archive está vacía:

Output
Empty set (0.000 sec)

Ahora, si emite una consulta DELETE contra la tabla collectibles, cualquier fila de la tabla podría eliminarse sin dejar rastro.

Para remediarlo, creará un activador que se ejecutará antes de todas las consultas DELETE en la tabla coleccionables. El propósito de este activador es guardar una copia del objeto eliminado en la tabla de archivo antes de que ocurra la eliminación.

Ejecute el siguiente comando:

CREATE TRIGGER archive_before_delete BEFORE DELETE
ON collectibles
FOR EACH ROW
INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

El disparador se llama archive_before_delete y sucede ANTES de cualquier consulta DELETE en la tabla coleccionables . Para cada fila que se eliminará, se ejecutará la instrucción INSERT. A su vez, la declaración INSERT inserta una nueva fila en la tabla collectibles_archive con valores de datos tomados del registro OLD, que es el que está programado para eliminación: OLD.name se convierte en la columna name y OLD.value se convierte en la columna value.

La base de datos confirmará la creación del disparador:

Output
Query OK, 0 row affected (0.009 sec)

Con el disparador en su lugar, intente eliminar un coleccionable de la tabla principal de coleccionables:

DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

El resultado confirma que la consulta se ejecutó correctamente:

Output
Query OK, 1 row affected (0.004 sec)

Ahora, enumere todos los coleccionables:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+----------------+-------+
| name           | value |
+----------------+-------+
| AIRCRAFT MODEL | 10.00 |
+----------------+-------+
1 row in set (0.000 sec)

Ahora solo queda MODELO DE AERONAVE; el MODELO DE NAVE ha sido eliminado y ya no está en la tabla. Sin embargo, con el disparador creado anteriormente, esta eliminación debe registrarse en la tabla collectibles_archive. Comprobemos eso.

Ejecutar otra consulta:

SELECT * FROM collectibles_archive;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+---------------------+
| name            | value | removed_on          |
+-----------------+-------+---------------------+
| SPACESHIP MODEL | 15.00 | 2022-11-20 11:32:01 |
+-----------------+-------+---------------------+
1 row in set (0.000 sec)

El disparador anotó automáticamente la eliminación en esa tabla. Las columnas name y value se han rellenado con datos de la fila que se eliminó. La tercera columna, removed_on, no se establece explícitamente a través del activador definido, por lo que toma el valor predeterminado decidido durante la creación de la tabla: la fecha de creación de cualquier fila nueva. Por eso, cada entrada agregada con la ayuda del activador siempre se anotará con la fecha de eliminación.

Con este disparador en su lugar, ahora puede estar seguro de que todas las consultas de DELETE darán como resultado una entrada de registro en collectibles_archive, dejando atrás información sobre coleccionables de propiedad anterior.

En la siguiente sección, usará activadores ejecutados después de las declaraciones de activación para actualizar la tabla de resumen con valores agregados basados en todos los coleccionables.

Uso de disparadores DESPUÉS DE INSERTAR, DESPUÉS DE ACTUALIZAR y DESPUÉS DE ELIMINAR para ejecutar acciones después de la manipulación de datos

En las dos secciones anteriores, usó disparadores ejecutados antes de las declaraciones principales para realizar operaciones basadas en los datos originales antes de actualizar la base de datos. En esta sección, actualizará la tabla de resumen con un recuento siempre actualizado y el valor acumulado de todos los coleccionables mediante disparadores que se ejecutan después de las declaraciones previstas. De esta manera, se asegurará de que los datos de la tabla de resumen tengan en cuenta el estado actual de la base de datos.

Comience examinando la tabla collectibles_stats:

SELECT * FROM collectibles_stats;

Dado que aún no ha agregado información a esta tabla, la cantidad de artículos coleccionables en propiedad es 0 y, por lo tanto, el valor acumulado es NULL:

Output
+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

Dado que no hay disparadores para esta tabla, las consultas emitidas anteriormente para insertar y actualizar coleccionables no afectaron esta tabla.

El objetivo es establecer los valores en una sola fila en la tabla collectibles_stats para presentar información actualizada sobre el recuento y el valor total de los coleccionables. Desea asegurarse de que el contenido de la tabla se actualice después de cada operación INSERT, UPDATE o DELETE.

Puede hacerlo creando tres disparadores separados, todos ejecutados después de la consulta correspondiente. Primero, cree el activador AFTER INSERT:

CREATE TRIGGER stats_after_insert AFTER INSERT
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

El disparador se llama stats_after_insert y ejecutará DESPUÉS cada INSERT consulta a los coleccionables tabla, ejecutando la instrucción UPDATE en el cuerpo del disparador. La consulta UPDATE afecta a collectibles_stats y establece las columnas count y value en los valores devueltos por las consultas anidadas:

• SELECT COUNT(name) FROM coleccionables obtendrá el recuento de coleccionables.
• SELECT SUM(value) FROM coleccionables obtendrá el valor total de todos los coleccionables.

La base de datos confirmará la creación del disparador:

Output
Query OK, 0 row affected (0.009 sec)

Ahora, intente volver a insertar el modelo de nave espacial previamente eliminado en la tabla de coleccionables para verificar si la tabla de resumen se actualizará correctamente:

INSERT INTO collectibles VALUES ('spaceship model', 15.00);

La base de datos imprimirá el siguiente mensaje de éxito:

Output
Query OK, 1 row affected (0.009 sec)

Puede enumerar todos los coleccionables que posee ejecutando:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| AIRCRAFT MODEL  | 10.00 |
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

Hay dos artículos coleccionables por valor de 25,00 en total. Para examinar la tabla de resumen después del elemento recién insertado, ejecute la siguiente consulta:

SELECT * FROM collectibles_stats;

Esta vez, la tabla de resumen enumerará el número de artículos coleccionables de propiedad como 2 y el valor acumulado como 25.00, que coincide con el resultado anterior:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 25.00 |
+-------+-------+
1 row in set (0.000 sec)

El activador stats_after_insert se ejecuta después de la consulta INSERT y actualiza la tabla collectibles_stats con los datos actuales (count y value) sobre la colección. Se recopilan estadísticas sobre todo el contenido de la colección, no solo sobre la última inserción. Dado que la colección ahora contiene dos elementos (modelos de aviones y naves espaciales), la tabla de resumen enumera dos elementos y su valor total. En este punto, agregar cualquier artículo coleccionable nuevo a la tabla de coleccionables actualizará la tabla de resumen con los valores correctos.

Sin embargo, la actualización de elementos existentes o la eliminación de coleccionables no afectará en absoluto al resumen. Para llenar ese vacío, creará dos activadores adicionales, realizando operaciones idénticas pero activados por diferentes eventos:

CREATE TRIGGER stats_after_update AFTER UPDATE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

CREATE TRIGGER stats_after_delete AFTER DELETE
ON collectibles
FOR EACH ROW
UPDATE collectibles_stats
SET count = (
    SELECT COUNT(name) FROM collectibles
), value = (
    SELECT SUM(value) FROM collectibles
);

Ahora ha creado dos activadores nuevos: stats_after_update y stats_after_delete. Ambos activadores se ejecutarán en la tabla collectible_stats siempre que ejecute una instrucción UPDATE o DELETE en la tabla collectibles.

La creación exitosa de esos disparadores imprimirá el siguiente resultado:

Output
Query OK, 0 row affected (0.009 sec)

Ahora, actualice el valor del precio de uno de los coleccionables:

UPDATE collectibles SET value = 25.00 WHERE name = 'AIRCRAFT MODEL';

La cláusula WHERE filtra la fila que se actualizará por nombre, y la cláusula SET cambia el valor a 25.00.

El resultado confirma que la declaración cambió una sola fila:

Output
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Una vez más, verifique el contenido de la tabla de resumen después de la actualización:

SELECT * FROM collectibles_stats;

El value ahora muestra 40.00, que es el valor correcto después de la actualización:

Output
+-------+-------+
| count | value |
+-------+-------+
|     2 | 40.00 |
+-------+-------+
1 row in set (0.000 sec)

El último paso es verificar que la tabla de resumen refleje correctamente la eliminación de un coleccionable. Intente eliminar el modelo de avión con la siguiente declaración:

DELETE FROM collectibles WHERE name = 'AIRCRAFT MODEL';

El siguiente resultado confirma que la consulta se ejecutó correctamente:

Output
Query OK, 1 row affected (0.004 sec)

Ahora, enumere todos los coleccionables:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
+-----------------+-------+
1 row in set (0.000 sec)

Ahora solo queda MODELO DE NAVE ESPACIAL. A continuación, verifique los valores en la tabla de resumen:

SELECT * FROM collectibles_stats;

Se imprimirá el siguiente resultado:

Output
+-------+-------+
| count | value |
+-------+-------+
|     1 | 15.00 |
+-------+-------+
1 row in set (0.000 sec)

La columna count ahora muestra que solo hay un coleccionable en la tabla principal. El valor total es 15,00, que coincide con el valor del MODELO DE NAVE.

Estos tres disparadores funcionan juntos después de las consultas INSERT, UPDATE y DELETE para mantener la tabla de resumen sincronizada con la lista completa de coleccionables.

En la siguiente sección, aprenderá a manipular los disparadores existentes en la base de datos.

Listado y eliminación de disparadores

En las secciones anteriores, creó nuevos disparadores. Dado que los disparadores son objetos con nombre definidos en la base de datos, al igual que las tablas, también puede enumerarlos y manipularlos cuando sea necesario.

Para enumerar todos los disparadores, ejecute la instrucción SHOW TRIGGERS:

SHOW TRIGGERS;

La salida incluirá todos los activadores, incluidos sus nombres, el evento desencadenante con el tiempo (ANTES o DESPUÉS de la ejecución de la instrucción), así como las instrucciones que forman parte del cuerpo del activador y otros detalles extensos de la definición del disparador:

Output, simplified for readability
+-------------------------+--------+--------------+--------(...)+--------+(...)
| Trigger                 | Event  | Table        | Statement   | Timing |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
| uppercase_before_insert | INSERT | collectibles | SET    (...)| BEFORE |(...)
| stats_after_insert      | INSERT | collectibles | UPDATE (...)| AFTER  |(...)
| uppercase_before_update | UPDATE | collectibles | SET    (...)| BEFORE |(...)
| stats_after_update      | UPDATE | collectibles | UPDATE (...)| AFTER  |(...)
| archive_before_delete   | DELETE | collectibles | INSERT (...)| BEFORE |(...)
| stats_after_delete      | DELETE | collectibles | UPDATE (...)| AFTER  |(...)
+-------------------------+--------+--------------+--------(...)+--------+(...)
6 rows in set (0.001 sec)

Para eliminar disparadores existentes, puede usar sentencias SQL DROP TRIGGER. Tal vez ya no desee imponer letras mayúsculas para los nombres coleccionables, por lo que ya no necesitará uppercase_before_insert y uppercase_before_update. Ejecute los siguientes comandos para eliminar estos dos disparadores:

DROP TRIGGER uppercase_before_insert;
DROP TRIGGER uppercase_before_update;

Para ambos comandos, MySQL responderá con un mensaje de éxito:

Output
Query OK, 0 rows affected (0.004 sec)

Ahora, con los dos gatillos desaparecidos, agreguemos un nuevo coleccionable en minúsculas:

INSERT INTO collectibles VALUES ('ship model', 10.00);

La base de datos confirmará la inserción:

Output
Query OK, 1 row affected (0.009 sec)

Puede verificar que la fila se insertó ejecutando la consulta SELECT:

SELECT * FROM collectibles;

El siguiente resultado se imprimirá en la pantalla:

Output
+-----------------+-------+
| name            | value |
+-----------------+-------+
| SPACESHIP MODEL | 15.00 |
| ship model      | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

El coleccionable recién agregado está en letras minúsculas. Dado que el nombre no se modifica con respecto a la salida original, ha confirmado que el disparador que convirtió previamente el caso de la letra ya no está en uso.

Ahora sabe cómo enumerar y eliminar activadores por nombre.

Conclusión

Al seguir esta guía, aprendió qué son los disparadores de SQL y cómo usarlos en MySQL para manipular datos antes de las consultas INSERT y UPDATE. Aprendió a usar el disparador BEFORE DELETE para archivar la fila eliminada en una tabla separada, así como a usar disparadores de declaraciones AFTER para mantener los resúmenes constantemente actualizados.

Puede usar funciones para descargar parte de la manipulación y validación de datos al motor de la base de datos, asegurando la integridad de los datos u ocultando algunos de los comportamientos de la base de datos del usuario diario de la base de datos. Este tutorial cubrió solo los conceptos básicos del uso de activadores para ese propósito. Puede crear activadores complejos que constan de varias declaraciones y usar lógica condicional para realizar acciones de forma aún más granular. Para obtener más información al respecto, consulte la documentación de MySQL sobre disparadores.

Si desea obtener más información sobre los diferentes conceptos relacionados con el lenguaje SQL y cómo trabajar con él, le recomendamos que consulte las otras guías de la serie Cómo usar SQL.