Diferencia entre revisiones de «BD UD7 Triggers»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 288: Línea 288:
 
         FROM ARTISTAS
 
         FROM ARTISTAS
 
         WHERE nif NOT IN (SELECT nif_jefe
 
         WHERE nif NOT IN (SELECT nif_jefe
        FROM ARTISTAS)
+
                          FROM ARTISTAS)
 
         LIMIT 1;
 
         LIMIT 1;
 
     END IF;
 
     END IF;

Revisión del 10:16 12 may 2022

Introducción

Nota: Todos los ejemplos están basados en esta base de datos.


  • Un trigger viene a ser como un procedimiento almacenado que se ejecuta automáticamente cuando sobre una tabla se realiza alguna operación que implique modificar sus datos (DELETE, INSERT, UPDATE).
Por lo tanto un trigger va a estar 'asociado' a una tabla y a un 'tipo de operación' sobre la tabla.
El trigger no tiene que realizar la operación sobre la tabla. La operación sobre la tabla viene a través de una orden SQL (UPDATE, DELETE, INSERT) que se ejecuta. Después o antes de que se ejecute esa orden, por cada fila modificada de la tabla se va a ejecutar el conjunto de instrucciones del trigger.


  • Los usos que le podemos dar a un trigger son:
  • Monitorizar y registrar operaciones sobre las tablas (podemos guardar información sobre quien y cuando se realizó alguna modificación).
  • Verificar que los datos sean correctos antes de añadirlos o usarlos para una modificación.
  • Un mecanismo para implementar columnas calculadas (por ejemplo, la tabla ATRACCIONES tiene una columna 'ganancias' y disponemos de la tabla ATRACCION_DIA, donde guardamos las ganancias por día. La columna ganancias de la tabla ATRACCIONES, por cada atracción, debe ser la suma de las ganancias de todos los días en los que se celebró esa atracción).
  • Copias de seguridad de los datos antes de que sean modificados o borrados.


  • Debemos de tener en cuenta que un trigger no se va a ejecutar en caso de una operación en cascada debido a las restricciones de integridad referencial (clave primaria-clave foránea).


  • Un trigger se puede definir para que se ejecute antes (before) o después (after) que la orden SQL que provoca su ejecución.
Normalmente se usa el tipo BEFORE para chequear que los datos son correctos antes de que sean incorporados a la tabla. Recordar que la verificación podemos implementarla con el uso de CHECK's cuando creamos la tabla, pero esta funcionalidad sólo está disponible a partir de la versión 8.0 de Mysql y dependiendo de la complejidad de la condición de verificación podría no ser posible realizarla haciendo uso de check.
El tipo AFTER se suele utilizar para columnas calculadas o para realizar o registrar las operaciones una vez que estas son realizadas en la tabla.


Operaciones con triggers

Crear Triggers

Disponéis de varios ejemplos en este enlace.


Mod BD Prog Trigger 1.jpg
Imagen obtenida de https://dev.mysql.com


  • Debéis utilizar la orden DELIMITER al igual que con los procedimientos almacenados, si los creáis desde una ventana de ejecución de consultas.


  • Es necesario tener el permiso TRIGGER otorgado para poder crear un trigger.
Dicho permiso se encuentra en el nivel de seguridad de tablas, por lo que es necesario indicar sobre qué tabla va a tener permiso para crear el trigger.


  • Básicamente cuando decidimos crear un trigger necesitamos determinar:
  • Sobre qué tabla va a aplicarse el trigger.
  • Sobre qué operación se va a aplicar (INSERT / DELETE / UPDATE)
  • Queremos que el trigger se ejecute antes o después de que se realice la operación sobre la tabla (BEFORE => antes; AFTER => después).


  • Indicar que si la operación SQL que va a provocar la ejecución del trigger afecta a múltiples filas (por ejemplo un borrado de muchas filas), el trigger se ejecutará una vez por cada fila afectada.


  • Dentro del trigger vamos a poder acceder a los valores de la fila de la tabla anterior y posterior a la ejecución de la orden sql. Es decir, si realizo una operación de UPDATE sobre una columna, dicha columna tendrá un valor antes de la ejecución de la orden SQL y otro después de la ejecución, ya que le estoy enviando un nuevo valor. Dentro del trigger puedo acceder a dos 'alias' de la tabla a la que afecta el trigger con las mismas columnas que la tabla original:
  • NEW: Tabla que posee los datos de cada columna con los nuevos valores.
  • OLD: Tabla que posee los datos de cada columna con los valores antiguos.
Por ejemplo:
TABLA ALUMNOS:
---nif---- ---nombre----
---1a----- ---Angel-----
Realiza una operación UPDATE con la orden: UPDATE ALUMNOS SET nombre = 'Pedro' WHERE nif='1a'
Si tengo asociado un trigger UPDATE (de cualquiera de los dos tipos, before/after), dentro del código del trigger:
  • NEW.nombre tendrá el valor 'Pedro'.
  • OLD.nombre tendrá el valor 'Angel'.


Operaciones
  • UPDATE:
  • NEW: Valores nuevos
  • OLD: Valores antiguos
  • INSERT
  • NEW: Valores nuevos
  • OLD: NO EXISTE
  • DELETE
  • NEW: NO EXISTE
  • OLD: Valores antiguos


  • La orden LOAD DATA también provoca la ejecución del trigger ya que realiza operaciones de INSERT sobre las tablas.


  • Es posible tener más de un trigger sobre la misma tabla y con el mismo evento.
En estos casos, la ejecución se hará uno después de otro en el orden en que fueron creados.
Si queremos modificar dicho orden, a la hora de crear el trigger tendremos que utilizar la opción trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
De tal forma que el trigger que se está creando se ejecute antes (PRECEDES) o después (FOLLOWS) que el trigger que ya existe (other_trigger_name)


  • Cuando se define un trigger, este estás asociado al usuario que lo creó (CREATE DEFINER=`root`@`localhost` TRIGGER .....)
Dicho usuario debe tener el permiso TRIGGER otorgado con la orden grant sobre la tabla, lo que le da derecho a crear, borrar, mostrar y ejecutar un trigger.
Cuando otro usuario realiza una operación SQL sobre una tabla en la que está asociado el trigger, el usuario que creó el trigger debe conservar el permiso TRIGGER para que se pueda ejecutar.


  • Dentro del cuerpo del trigger vamos a poder acceder a los valores antiguos y nuevos como hemos visto.
  • Para poder modificar el valor nuevo (SET NEW.col=valor) el usuario que creó el trigger necesita tener permiso UPDATE sobre la tabla.
  • Para poder consultar el valor nuevo o antiguo (OLD.col o NEW.col) el usuario que creó el trigger necesita tener el permiso SELECT sobre la tabla.


Gráficamente


Visualizar triggers

  • La orden SQL para ver los triggers asociados a una base de datos o a una tabla es SHOW TRIGGERS.
  • Por defecto muestra los triggers de la base de datos activa. Si queremos mostrar los de otra debemos de utilizar la opción FROM nombre_BD o IN nombre_BD.
  • Si queremos buscar por los triggers asociados a una determinada tabla podemos hacer uso de la cláusula LIKE que busca por patrón (símbolo comodín el %) nombres de tablas.
  • Si queremos buscar dentro de los resultados aquellas filas que cumplan alguna condición asociada al valor de alguna columna debemos hacer uso la cláusula WHERE. Por ejemplo: show triggers where `Definer` like 'root%'
Fijarse que en este caso, en la parte where se pueden emplear todas las 'columnas' que aparecen cuando ejecutamos la orden SHOW TRIGGER. Dichas columnas deben ir entre ` (acento grave).
  • Si queremos obtener información sobre un trigger específico por su nombre, debemos hacer uso de la orden SQL SHOW CREATE TRIGGER.
  • La base de datos y tabla donde se guarda toda la información de los triggers es: information_schema.triggers


Modificar triggers

  • La modificación de triggers NO es posible: es necesario borrar y volver a crear el trigger.


Borrar triggers


Aspectos varios

Nombre de usuario que realiza la operación

  • Como comentamos al principio, uno de los objetivos de utilizar triggers era el de registrar las operaciones sobre las tablas.
Uno de los datos que puede que necesitemos es el de obtener el nombre del usuario que realiza la operación.
Para obtenerlo podemos hacer uso de la función USER().
Cuidado con utilizar la función CURRENT_USER() ya que esta devuelve el usuario que creó el trigger

Funciones interesantes

  • NOW(): obtiene el DATETIME actual
  • SYSDATE(): obtiene el DATETIME actual
  • CURDATE(): Obtiene la fecha actual.
  • DATE(SYSDATE()): obtiene el DATE actual
  • TIME(SYSDATE()): obtiene el TIME actual
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'mensaje a mostrar' (la veremos en control de errores)
Cancela la operación que se estaba realizando y muestra un mensaje.
Lleva el valor 45000 ya que este valor significa 'excepción definida por el usuario' (“unhandled user-defined exception”)
Si queremos que los días-meses aparezcan en español, lo podemos cambiar con la variable del sistema lc_time_names o localmente con la orden: SET lc_time_names = 'es_ES';


Ejercicios propuestos Triggers

Ejercicio 1

Haz que no se pueda añadir un nuevo animal si el tipo es 'León' y el número de años es mayor que 20.

En caso de no cumplirse la condición lanzará una excepción.
Nota: Fijarse que si el alta la realizamos a través de un procedimiento almacenado, la comprobación ya puede ir allí y no necesitaríamos el uso de triggers.
Siempre es mejor emplear procedimientos que triggers sobre los cuales no tenemos control.

Ejercicio 2

Cuando se añada un nuevo animal, hacer que dicho animal esté cuidado por el artista que cuida a menos animales.

Deberás de tener en cuenta el caso en el que no haya ningún artista cuidando animales. En ese caso debes coger el primer artista que no sea jefe.
Fijarse que este trigger va a suponer que se añaden dos filas: una fila a la tabla ANIMALES y otra a la tabla ANIMALES_ARTISTAS. Por lo tanto, si queremos que ambas operaciones se hagan juntas, debería ir dentro de una transacción y controlar si el trigger provoca un error para hacer el ROLLBACK. El ROLLBACK no se puede hacer dentro del trigger. Habría que empezar la transacción en el código donde se realizar la orden SQL INSERT (por ejemplo en el programa cliente que esté realizando el programador o dentro de un procedimiento almacenado).

Ejercicio 3

Haz todo lo necesario para que el campo ganancias de la tabla ATRACCIONES se actualice cuando se añadan, borren o modifiquen datos en la tabla ATRACCION_DIA.

Si al añadir una celebración nueva (ATRACCION_DIA) la fecha_inicio en ATRACCIONES es NULL, se debe de actualizar con la fecha actual.
Nota: Debéis de tener en cuenta que tanto la fecha como las ganancias en ATRACCIONES pueden tener valores nulos. Hacer uso de la función IFNULL para dar un valor a esas columnas cuando sean null (recordar que si sumamos una columna con valor null siempre devolverá null).

Ejercicio 4

Modifica la tabla ATRACCIONES y añade una nueva columna de nombre contador, de tipo numérico y valor por defecto de cero, que lleve cuenta de cuantas veces se ha celebrado la atracción y que se actualice con cualquier operación. Crea los triggers necesarios.

Nota: Los datos ya añadidos tendrán como valor null. Realiza una operación de UPDATE para ponerlos a su valor correcto.

Ejercicio 5

Impide que se pueda añadir o modificar una pista con un aforo superior a 1000 o inferior a 10 (esto lo podríamos implementar con un check, pero vamos a practicar el uso de triggers).

Ejercicio 6

Haz que si se intenta dar de alta un nuevo artista y se envía un nif_jefe que no exista, se cambie su valor por null y se informe al usuario mostrando el valor -1 (con un select).

Nota: En este caso se muestra como también se pueden modificar los valores que se van a añadir dentro de un trigger.
Recordar que la operación SQL se está ejecutando fuera del trigger. No debéis de repetir el INSERT dentro del trigger. Sólo cambiar el valor de la columna en el alias adecuado.

Ejercicio 7

Crea una tabla de nombre REGISTRO con las columnas:

  • id autonumérica Clave primaria
  • usuario: varchar(100)
  • tabla: varchar(100)
  • operacion: varchar(10)
  • datos_antiguos: varchar(100) (guardarán los datos nombre_pista:aforo que se borren o modifiquen)
  • datos_nuevos: varchar(100) (guardarán los datos nombre_pista:aforo que se añadan o modifiquen)
  • fecha-hora: datetime
  • Haz que se registren las operaciones de alta, baja y modificación sobre la tabla 'PISTAS'.
Por ejemplo:
  • 'angel' - 'PISTAS' - 'ALTA' - null - 'pista_nueva:1000' - '01-01-2000 17:00:00' (el campo datos_antiguos es nulo ya que estamos a dar de alta una nueva pista).
  • 'luis' - 'PISTAS' - 'BAJA' - 'pista_borrar:1000' - null - '01-01-2000 18:00:00' (el campo datos_nuevos es nulo ya que estamos a dar de baja una pista).
  • 'pepe' - 'PISTAS' - 'MODIFICAR' - 'pista_modificar:1000' - 'pista_modificar:1500' - '01-01-2000 19:00:00' (el campo datos_nuevos guarda los datos modificados y el campo datos_antiguos guarda los datos antes de la modificación).
Si ya existen triggers creados, nos los modifiques, crea nuevos.

Ejercicio 8

Crea una tabla de nombre CONTADOR con las columnas:

  • id autonumérica Clave primaria
  • tipo: varchar(100) no nulo
  • valor: int no nulo
Añade dos filas con los valores para tipo/valor: pistas/0 animales/0
Haz que cada vez que haya alguna operación que modifique (alta/baja) el número de pistas o de animales, se actualice el número total de los mismos.
Ejecuta la orden SQL que actualice la tabla contador con los datos actuales de las tablas.

Ejercicio 9

Ejecuta la orden SQL que muestre los triggers creados en la tabla PISTAS.

Ejercicio 10

Ejecuta la orden SQL que muestre la información del trigger del ejercicio 1 (de nombre animales_checkAdd_INSERT).


Solución Ejercicios propuestos Triggers

Ejercicio 1

Haz que no se pueda añadir un nuevo animal si el tipo es 'León' y el número de años es mayor que 20.

Nota: Fijarse que si el alta la realizamos a través de un procedimiento almacenado, la comprobación ya puede ir allí y no necesitaríamos el uso de triggers.
Siempre es mejor emplear procedimientos que triggers sobre los cuales no tenemos control.
Los Triggers serán necesarios cuando hagamos operaciones SQL directamente sobre las tablas y no dispongamos de un check que nos cubra la condición que queramos tener.
En caso de no cumplirse la condición lanzará una excepción.
Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => ANIMALES
  • Identificar la operación sobre la que se va a crear el trigger => INSERT
  • Identificar si queremos que el trigger se ejecuta antes o después: Como queremos comprobar una serie de condiciones y queremos impedir que se añada la fila si no se cumplen, queremos que el trigger se ejecute antes que la inserción de la fila => BEFORE
Para acceder a los datos que estamos queriendo añadir, debemos de hacer uso de la tabla NEW.
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS animales_checkAdd_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER animales_checkAdd_INSERT BEFORE INSERT ON ANIMALES FOR EACH ROW
 5 BEGIN
 6 	IF (NEW.tipo='León' AND NEW.anhos>20) THEN
 7 		SIGNAL SQLSTATE '45000' SET message_text='El tipo león no puede tener más de 20 años';
 8     END IF;
 9 END $$
10 DELIMITER ;


Si ahora intentáis añadir una nueva fila a la tabla ANIMALES con un animal de tipo 'León' y más de 20 años no os dejará. Comprobarlo.
1 INSERT INTO `CIRCO`.`ANIMALES` (`nombre`,`tipo`,`anhos`,`peso`,`estatura`,`nombre_atraccion`,`nombre_pista`)
2 VALUES ('El comehombres','León',25,120,1.2,'El gran felino','LATERAL1');
Indicar que la orden SIGNAL impide que se ejecuta la orden INSERT que desencadenó el trigger.

Ejercicio 2

Cuando se añada un nuevo animal, hacer que dicho animal esté cuidado por el artista que cuida a menos animales.

Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => ANIMALES
  • Identificar la operación sobre la que se va a crear el trigger => INSERT
  • Identificar si queremos que el trigger se ejecuta antes o después: Queremos añadir una nueva fila a la tabla ANIMALES_ARTISTAS. Por lo tanto, el animal tiene que estar ya añadido a la tabla para poder realizar la operación => AFTER
Para acceder a los datos que estamos queriendo añadir, debemos de hacer uso de la tabla NEW.
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS animales_addArtista_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER animales_addArtista_INSERT AFTER INSERT ON ANIMALES FOR EACH ROW
 5 BEGIN
 6     DECLARE v_nifArtista char(9);	-- Por defecto null
 7     DECLARE v_temp int default 0;
 8     
 9     -- Buscamos el artista que cuida a menos animales
10     SELECT nif_artista, COUNT(*) as num
11     INTO v_nifArtista,v_temp
12     FROM ANIMALES_ARTISTAS
13     GROUP BY nif_artista
14     ORDER BY num asc
15     LIMIT 1;
16     
17     IF (v_nifArtista IS NULL) THEN	-- No hay. Buscamos el primer artista que no sea jefe
18         SELECT nif
19         INTO v_nifArtista
20         FROM ARTISTAS
21         WHERE nif NOT IN (SELECT nif_jefe
22                           FROM ARTISTAS)
23         LIMIT 1;
24     END IF;
25 
26     IF (v_nifArtista IS NULL) THEN -- Error. En este caso se mantendría el animal en la tabla ya que el trigger es AFTER. Se tendría que hacer el control donde se hace la orden INSERT sobre la tabla ANIMALES
27         SIGNAL SQLSTATE '45000' SET message_text='No hay artistas para cuidar a animales';
28     END IF;
29     
30     INSERT INTO ANIMALES_ARTISTAS (nombre_animal,nif_artista)
31     VALUES (NEW.nombre, v_nifArtista);
32     
33 END $$
34 DELIMITER ;


Si ejecutamos este código debería asignar el animal al artista con nif 22222222B.
1 INSERT INTO `CIRCO`.`ANIMALES` (`nombre`,`tipo`,`anhos`,`peso`,`estatura`,`nombre_atraccion`,`nombre_pista`)
2 VALUES ('El comehombres','León',2,120,1.2,'El gran felino','LATERAL1');

Ejercicio 3

Haz todo lo necesario para que el campo ganancias de la tabla ATRACCIONES se actualice cuando se añadan, borren o modifiquen datos en la tabla ATRACCION_DIA.

Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => ATRACCIONES
  • Identificar la operación sobre la que se va a crear el trigger => INSERT, UPDATE, DELETE
  • Identificar si queremos que el trigger se ejecuta antes o después: Queremos actualizar el campo ganancias una vez se ha actualizado la fila ATRACCION_DIA por lo tanto el trigger tiene que ser AFTER.
Para acceder a los datos que estamos queriendo AÑADIR, debemos de hacer uso de la tabla NEW.
Para acceder a los datos que estamos queriendo BORRAR, debemos de hacer uso de la tabla OLD.
Para acceder a los datos que estamos queriendo MODIFICAR, debemos de hacer uso de la tabla OLD para acceder a los viejos y NEW para acceder a los nuevos.


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS atracciones_actualizarGananciasTotales_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER atracciones_actualizarGananciasTotales_INSERT AFTER INSERT ON ATRACCION_DIA FOR EACH ROW
 5 BEGIN
 6     DECLARE v_fecha date;
 7 
 8     SELECT IFNULL(fecha_inicio,CURDATE())
 9     INTO v_fecha
10     FROM ATRACCIONES
11     WHERE nombre = NEW.nombre_atraccion;
12     
13     UPDATE ATRACCIONES
14     SET ganancias = IFNULL(ganancias,0) + NEW.ganancias,
15         fecha_inicio = v_fecha
16     WHERE nombre = NEW.nombre_atraccion;
17     
18 END $$
19 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS atracciones_actualizarGananciasTotales_UPDATE;
 3 DELIMITER $$
 4 CREATE TRIGGER atracciones_actualizarGananciasTotales_UPDATE AFTER UPDATE ON ATRACCION_DIA FOR EACH ROW
 5 BEGIN
 6     UPDATE ATRACCIONES
 7     SET ganancias = ganancias + NEW.ganancias - OLD.ganancias
 8     WHERE nombre = OLD.nombre_atraccion;
 9     
10 END $$
11 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS atracciones_actualizarGananciasTotales_DELETE;
 3 DELIMITER $$
 4 CREATE TRIGGER atracciones_actualizarGananciasTotales_DELETE AFTER DELETE ON ATRACCION_DIA FOR EACH ROW
 5 BEGIN
 6     UPDATE ATRACCIONES
 7     SET ganancias = ganancias - OLD.ganancias
 8     WHERE nombre = OLD.nombre_atraccion;
 9     
10 END $$
11 DELIMITER ;
Si ejecutamos este código deberían de ejecutarse cada uno de los trigger (comprobar los datos en la tabla ATRACCIONES):
1 INSERT INTO `CIRCO`.`ATRACCION_DIA` (`nombre_atraccion`, `fecha`, `num_espectadores`, `ganancias`) VALUES ('El orangután', '2020-03-02', '500', '30000.00');
1 INSERT INTO `CIRCO`.`ATRACCION_DIA` (`nombre_atraccion`, `fecha`, `num_espectadores`, `ganancias`) VALUES ('El orangután', '2020-03-05', '100', '10000.00');
1 UPDATE `CIRCO`.`ATRACCION_DIA` SET `ganancias` = '35000.00' WHERE (`nombre_atraccion` = 'El orangután') and (`fecha` = '2020-03-02');
1 DELETE FROM `CIRCO`.`ATRACCION_DIA` WHERE (`nombre_atraccion` = 'El orangután') and (`fecha` = '2020-03-05');

Ejercicio 4

Modifica la tabla ATRACCIONES y añade una nueva columna de nombre contador, de tipo numérico y valor por defecto de cero, que lleve cuenta de cuantas veces se ha celebrado la atracción y que se actualice con cualquier operación. Crea los triggers necesarios.

Nota: Los datos ya añadidos tendrán como valor null. Realiza una operación de UPDATE para ponerlos a su valor correcto.
Sentencia SQL para actualizar la columna contador en las filas ya existentes:
Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => ATRACCION_DIA (es la tabla que guarda los días en los que se celebran atracciones)
  • Identificar la operación sobre la que se va a crear el trigger => INSERT, DELETE (UPDATE no es necesario ya que no va a variar el número de atracciones)
  • Identificar si queremos que el trigger se ejecuta antes o después: Queremos actualizar el campo contador una vez se ha añadido o borrado la fila ATRACCION_DIA por lo tanto el trigger tiene que ser AFTER.
Para acceder a los datos que estamos queriendo AÑADIR, debemos de hacer uso de la tabla NEW.
Para acceder a los datos que estamos queriendo BORRAR, debemos de hacer uso de la tabla OLD.


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS atracciones_numAtracc_DELETE;
 3 DELIMITER $$
 4 CREATE TRIGGER atracciones_numAtracc_DELETE AFTER DELETE ON ATRACCION_DIA FOR EACH ROW
 5 BEGIN
 6     UPDATE ATRACCIONES
 7     SET contador = contador - 1
 8     WHERE nombre = OLD.nombre_atraccion;
 9     
10 END $$
11 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS atracciones_numAtracc_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER atracciones_numAtracc_INSERT AFTER INSERT ON ATRACCION_DIA FOR EACH ROW
 5 BEGIN
 6     UPDATE ATRACCIONES
 7     SET contador = contador + 1
 8     WHERE nombre = NEW.nombre_atraccion;
 9     
10 END $$
11 DELIMITER ;
Si ejecutamos este código deberían de ejecutarse cada uno de los trigger (comprobar los datos en la tabla ATRACCIONES):
1 INSERT INTO `CIRCO`.`ATRACCION_DIA` (`nombre_atraccion`, `fecha`, `num_espectadores`, `ganancias`) VALUES ('La gigante', '2020-04-01', '120', '11232');   -- La gigante tendrá una atracción en contador
1 INSERT INTO `CIRCO`.`ATRACCION_DIA` (`nombre_atraccion`, `fecha`, `num_espectadores`, `ganancias`) VALUES ('La gigante', '2020-04-02', '220', '21232.00'); -- La gigante pasa a tener dos en contador
1 DELETE FROM `CIRCO`.`ATRACCION_DIA` WHERE (`nombre_atraccion` = 'La gigante') and (`fecha` = '2020-04-01');    -- La gigante pasa a tener uno en contador

Ejercicio 5

Impide que se pueda añadir o modificar una pista con un aforo superior a 1000 o inferior a 10 (esto lo podríamos implementar con un check, pero vamos a practicar el uso de triggers).

Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => PISTAS
  • Identificar la operación sobre la que se va a crear el trigger => INSERT, UPDATE
  • Identificar si queremos que el trigger se ejecuta antes o después: Como queremos 'impedir' que se añadan o modifiquen los datos cuando no se cumple la condición, es de tipo BEFORE.
Para acceder a los datos que estamos queriendo AÑADIR, debemos de hacer uso de la tabla NEW.
Para acceder a los datos que estamos queriendo MODIFICAR, debemos de hacer uso de la tabla OLD para acceder a los viejos y NEW para acceder a los nuevos.


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_checkAforo_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_checkAforo_INSERT BEFORE INSERT ON PISTAS FOR EACH ROW
 5 BEGIN
 6 
 7     IF (NEW.aforo < 10 OR NEW.aforo > 1000) THEN
 8         SIGNAL SQLSTATE '45000' SET message_text='Al aforo debe estar entre 10 y 1000';
 9     END IF;
10 
11 END $$
12 DELIMITER ;


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_checkAforo_UPDATE;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_checkAforo_UPDATE BEFORE UPDATE ON PISTAS FOR EACH ROW
 5 BEGIN
 6 
 7     IF (NEW.aforo < 10 OR NEW.aforo > 1000) THEN
 8         SIGNAL SQLSTATE '45000' SET message_text='Al aforo debe estar entre 10 y 1000';
 9     END IF;
10 
11 END $$
12 DELIMITER ;


Si ejecutamos este código deberían de ejecutarse cada uno de los trigger (comprobar los datos en la tabla PISTAS):
1 UPDATE `CIRCO`.`PISTAS` SET `aforo` = '1' WHERE (`nombre` = 'SUPER');
1 INSERT INTO `CIRCO`.`PISTAS` (`nombre`, `aforo`) VALUES ('ELEVADA', '20000');

Ejercicio 6

Haz que si se intenta dar de alta un nuevo artista y se envía un nif_jefe que no exista, se cambie su valor por null y se informe al usuario mostrando el valor -1 (con un select).

Nota: En este caso se muestra como también se pueden modificar los valores que se van a añadir dentro de un trigger.
Recordar que la operación SQL se está ejecutando fuera del trigger. No debéis de repetir el INSERT dentro del trigger. Sólo cambiar el valor de la columna en el alias adecuado.


Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => ARTISTAS
  • Identificar la operación sobre la que se va a crear el trigger => INSERT
  • Identificar si queremos que el trigger se ejecuta antes o después: Como queremos 'modificar' el dato a añadir cuando no se cumple la condición, es de tipo BEFORE.
Para acceder a los datos que estamos queriendo AÑADIR, debemos de hacer uso de la tabla NEW.


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS artistas_addCheckJefe;
 3 DELIMITER $$
 4 CREATE TRIGGER artistas_addCheckJefe BEFORE INSERT ON ARTISTAS FOR EACH ROW
 5 BEGIN
 6     DECLARE v_existeJefe tinyint default 0;    -- Esta vez comparamos en la condición siguiente con el valor 0 en vez de con null
 7     
 8     SELECT COUNT(*)
 9     INTO v_existeJefe
10     FROM ARTISTAS
11     WHERE nif = NEW.nif_jefe;
12     
13     IF (v_existeJefe = 0) THEN
14      SET NEW.nif_jefe = NULL;
15     END IF;
16     
17     
18 END $$
19 DELIMITER ;


Si ejecutamos este código deberían de ejecutarse cada uno de los trigger (comprobar los datos en la tabla ARTISTAS):
1 INSERT INTO `CIRCO`.`ARTISTAS` (`nif`, `apellidos`, `nombre`, `nif_jefe`) VALUES ('99999999I', 'Román Díaz', 'Eva', '12343333A');

Ejercicio 7

Crea una tabla de nombre REGISTRO con las columnas:

  • id autonumérica Clave primaria
  • usuario: varchar(100)
  • tabla: varchar(100)
  • operacion: varchar(10)
  • datos_antiguos: varchar(100) (guardarán los datos nombre_pista:aforo que se borren o modifiquen)
  • datos_nuevos: varchar(100) (guardarán los datos nombre_pista:aforo que se añadan o modifiquen)
  • fecha-hora: datetime
  • Haz que se registren las operaciones de alta, baja y modificación sobre la tabla 'PISTAS'.
Por ejemplo:
  • 'angel' - 'PISTAS' - 'ALTA' - null - 'pista_nueva:1000' - '01-01-2000 17:00:00' (el campo datos_antiguos es nulo ya que estamos a dar de alta una nueva pista).
  • 'luis' - 'PISTAS' - 'BAJA' - 'pista_borrar:1000' - null - '01-01-2000 18:00:00' (el campo datos_nuevos es nulo ya que estamos a dar de baja una pista).
  • 'pepe' - 'PISTAS' - 'MODIFICAR' - 'pista_modificar:1000' - 'pista_modificar:1500' - '01-01-2000 19:00:00' (el campo datos_nuevos guarda los datos modificados y el campo datos_antiguos guarda los datos antes de la modificación).
Si ya existen triggers creados, nos los modifiques, crea nuevos.


Solución: EL proceso para crear un trigger como comenté antes es:
  • Enunciado: Haz que se registren las operaciones de alta, baja y modificación sobre la tabla 'PISTAS'.
  • Identificar la tabla sobre la que vamos a crear el trigger => PISTAS
  • Identificar la operación sobre la que se va a crear el trigger => INSERT, UPDATE, DELETE
  • Identificar si queremos que el trigger se ejecuta antes o después: Como queremos registrar quien ha realizado el cambio, es necesario que el cambio esté realizado, por lo tanto es de tipo AFTER.
Para acceder a los datos que estamos queriendo AÑADIR, debemos de hacer uso de la tabla NEW.
Para acceder a los datos que estamos queriendo BORRAR, debemos de hacer uso de la tabla OLD.
Para acceder a los datos que estamos queriendo MODIFICAR, debemos de hacer uso de la tabla OLD para acceder a los viejos y NEW para acceder a los nuevos.


Creamos la tabla REGISTRO:
 1 USE CIRCO;
 2 CREATE TABLE `REGISTRO` (
 3   `id` int(11) NOT NULL AUTO_INCREMENT,
 4   `usuario` varchar(100) NOT NULL,
 5   `tabla` varchar(100) NOT NULL,
 6   `operacion` varchar(10) NOT NULL,
 7   `fecha-hora` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 8   `datos_antiguos` varchar(100) DEFAULT NULL,
 9   `datos_nuevos` varchar(100) DEFAULT NULL,
10   PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish2_ci;
Fijarse que el campo 'fecha-hora' tiene un valor por defecto que será la fecha-hora del sistema.


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_addRegistro_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_addRegistro_INSERT AFTER INSERT ON PISTAS FOR EACH ROW
 5 BEGIN
 6     
 7     INSERT INTO REGISTRO (usuario,tabla,operacion,datos_nuevos)
 8     VALUES (USER(),'PISTAS','ALTA',CONCAT(NEW.nombre,':',NEW.aforo));
 9     
10 END $$
11 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_addRegistro_DELETE;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_addRegistro_DELETE AFTER DELETE ON PISTAS FOR EACH ROW
 5 BEGIN
 6     
 7     INSERT INTO REGISTRO (usuario,tabla,operacion,datos_antiguos)
 8     VALUES (USER(),'PISTAS','BAJA',CONCAT(OLD.nombre,':',OLD.aforo));
 9     
10 END $$
11 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_addRegistro_UPDATE;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_addRegistro_UPDATE AFTER UPDATE ON PISTAS FOR EACH ROW
 5 BEGIN
 6     
 7     INSERT INTO REGISTRO (usuario,tabla,operacion,datos_antiguos,datos_nuevos)
 8     VALUES (USER(),'PISTAS','MODIFICAR',CONCAT(OLD.nombre,':',OLD.aforo),CONCAT(NEW.nombre,':',NEW.aforo));
 9 
10 END $$
11 DELIMITER ;


Si ejecutamos este código deberían de ejecutarse cada uno de los trigger (comprobar los datos en la tabla REGISTRO):
1 INSERT INTO `CIRCO`.`PISTAS` (`nombre`, `aforo`) VALUES ('LATERAL3', '120');
1 UPDATE `CIRCO`.`PISTAS` SET `aforo` = '150' WHERE (`nombre` = 'LATERAL3');
1 DELETE FROM `CIRCO`.`PISTAS` WHERE (`nombre` = 'LATERAL3');

Ejercicio 8

Crea una tabla de nombre CONTADOR con las columnas:

  • id autonumérica Clave primaria
  • tipo: varchar(100) no nulo
  • valor: int no nulo
Añade dos filas con los valores para tipo/valor: pistas/0 animales/0
Haz que cada vez que haya alguna operación que modifique (alta/baja) el número de pistas o de animales, se actualice el número total de los mismos.
Ejecuta la orden SQL que actualice la tabla contador con los datos actuales de las tablas.


Solución: EL proceso para crear un trigger como comenté antes es:
  • Identificar la tabla sobre la que vamos a crear el trigger => PISTAS / ANIMALES
  • Identificar la operación sobre la que se va a crear el trigger => INSERT, DELETE
  • Identificar si queremos que el trigger se ejecuta antes o después: Como queremos registrar quien ha realizado el cambio, es necesario que el cambio esté realizado, por lo tanto es de tipo AFTER.
Para acceder a los datos que estamos queriendo AÑADIR, debemos de hacer uso de la tabla NEW.
Para acceder a los datos que estamos queriendo BORRAR, debemos de hacer uso de la tabla OLD.


Creamos la tabla CONTADOR:
1 CREATE TABLE `CIRCO`.`CONTADOR` (
2   `id` INT NOT NULL AUTO_INCREMENT,
3   `tipo` VARCHAR(100) NOT NULL,
4   `valor` INT NOT NULL,
5   PRIMARY KEY (`id`));
Añadimos las filas:
1 INSERT INTO `CIRCO`.`CONTADOR` (`tipo`, `valor`) VALUES ('pistas', '0');
2 INSERT INTO `CIRCO`.`CONTADOR` (`tipo`, `valor`) VALUES ('animales', '0');


Orden SQL para actualizar los datos:

1 UPDATE CONTADOR
2 SET valor = (SELECT COUNT(*)
3              FROM PISTAS)
4 WHERE tipo = 'pistas';
5             
6 UPDATE CONTADOR
7 SET valor = (SELECT COUNT(*)
8              FROM ANIMALES)
9 WHERE tipo = 'animales';


Triggers:

 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS animales_gestContador_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER animales_gestContador_INSERT AFTER INSERT ON ANIMALES FOR EACH ROW
 5 BEGIN
 6   
 7   UPDATE CONTADOR
 8   SET valor = valor + 1
 9   WHERE tipo = 'animales';
10 
11 END $$
12 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS animales_gestContador_DELETE;
 3 DELIMITER $$
 4 CREATE TRIGGER animales_gestContador_DELETE AFTER DELETE ON ANIMALES FOR EACH ROW
 5 BEGIN
 6   
 7   UPDATE CONTADOR
 8   SET valor = valor - 1
 9   WHERE tipo = 'animales';
10 
11 END $$
12 DELIMITER ;
 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_gestContador_INSERT;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_gestContador_INSERT AFTER INSERT ON PISTAS FOR EACH ROW
 5 BEGIN
 6   
 7   UPDATE CONTADOR
 8   SET valor = valor + 1
 9   WHERE tipo = 'pistas';
10 
11 END $$
12 DELIMITER ;


 1 USE CIRCO;
 2 DROP TRIGGER IF EXISTS pistas_gestContador_DELETE;
 3 DELIMITER $$
 4 CREATE TRIGGER pistas_gestContador_DELETE AFTER DELETE ON PISTAS FOR EACH ROW
 5 BEGIN
 6   
 7   UPDATE CONTADOR
 8   SET valor = valor - 1
 9   WHERE tipo = 'pistas';
10 
11 END $$
12 DELIMITER ;


Si ejecutamos este código deberían de ejecutarse cada uno de los trigger (comprobar los datos en la tabla CONTADOR por cada sentencia):
1 INSERT INTO `CIRCO`.`PISTAS` (`nombre`, `aforo`) VALUES ('ESPECTACULAR', '250');
1 DELETE FROM `CIRCO`.`PISTAS` WHERE (`nombre` = 'ESPECTACULAR');
1 INSERT INTO `CIRCO`.`ANIMALES` (`nombre`, `tipo`, `anhos`, `peso`, `estatura`, `nombre_atraccion`, `nombre_pista`) VALUES ('Jaim', 'Mono', '1', '15', '.95', 'El gran carnívoro', 'SUPER');

Nota: Para borrar el animal debemos de borrar la relación del ANIMAL con el ARTISTA (ANIMALES_ARTISTAS) ya que hizimos un trigger anterior para que automaticamente al dar de alta un animal, lo cuidara un artista.

1 DELETE FROM `CIRCO`.`ANIMALES_ARTISTAS` WHERE (`nombre_animal` = 'Jaim') and (`nif_artista` = '44444444D');
2 DELETE FROM `CIRCO`.`ANIMALES` WHERE (`nombre` = 'Jaim');

Ejercicio 9

Ejecuta la orden SQL que muestre los triggers creados en la tabla PISTAS.

1 SHOW TRIGGERS IN CIRCO
2 WHERE `Table` = 'PISTAS';

O también (mejor la anterior que no busca por LIKE):

1 SHOW TRIGGERS IN CIRCO
2 LIKE 'PISTAS';

Ejercicio 10

Ejecuta la orden SQL que muestre la información del trigger del ejercicio 1 (de nombre animales_checkAdd_INSERT).

1 SHOW CREATE TRIGGER animales_checkAdd_INSERT;

O

1 SHOW TRIGGERS IN CIRCO
2 WHERE `Trigger` = 'animales_checkAdd_INSERT';


Enlace a la página principal de la UD7

Enlace a la página principal del curso


-- Ángel D. Fernández González -- (2020).