Introducción

Un procedimiento almacenado (PA) es una rutina (procedimiento o función) formada por un conjunto de ordenes SQL que se guarda en el servidor, como un objeto más de la BD.

En MySQL funcionan a partir de la versión 5. Para comprobar la versión:

sql> show variables like '%version%';

Desde dónde se puede invocar un PA:

  • De forma directa (por ejemplo: el entorno textual de MySQL o desde workbench)
  • Desde una aplicación que acceda a la BD (un programa java o PHP por ejemplo)
  • Desde otro PA

Notas

  • En la tabla mysql.proc ha información de los procedimientos creados: nombre, propietario, base de datos donde están, etc..
  • Si queremos utilizar tranasacciones dentro de un PE, en vez de utilizar BEGIN tendremos que hacerlo con START TRANSACTION

Ventajas e inconvenientes

En la figura que se muestra aparecen las dos arquitecturas de desarrollo e aplicaciones posibles, una hace uso de los PE y la otra no:

imagen arquitecturas

Ventajas

  1. Mejora el rendimiento puesto que no hay que enviar tanta informacion entre cliente y servidor. Se nota mas esa mejora si cliente y servidor no estan en la misma maquina.

  2. El PE puede ser utilizado por diferentes aplicaciones, independientemente del lenguaje y la plataforma.

  3. Facilita el trabajo al programador, puesto que él sabe que existe un PE que le proporciona un determinado servicio, no necesita saber las sentencias SQL que incorpora el procedimiento, ni los elementos del esquema de la BD.

  4. Seguridad. Podemos quitar permisos a los usuarios sobre la BD y dolo dar permisos sobre los procedimientos. Así controlamos de qué forma queremos que sean accedidas los datos.

Inconvenientes

  1. Existe una dependencia con el SGBD utilizado. Es decir, cada SGBD tiene su propio sublengiaje para los PA.
SGBD Subllenguatge SQL per a PE
MySQL SQL 2003
Oracle PL/SQL + Java
DB2 (IBM PL SQL + Java
SQL Server Transact-SQL

Por tanto si hacemos un PA con un determinado SGBD, tendremos que rehacer los procedimientos si cambiamos de SGBD

  1. No hay herramientas de depuración para los PA.
  2. No se permite la orden USE dentro de los PA para cambiar de BD. Pero podemos utilizar la sintaxis: nombre_bd.nombre_objeto para acceder a cualquier objeto de cualquier BD a la que tengamos acceso.

Tipos de procedimientos y funciones

Hay dos tipos de rutinas: - Procedimientos (Procedures) - Funciones (Functions)

Un procedimiento es un conjunto de tareas o acciones que no devuelven nada al usuario, en cambio, las funciones devuelven un valor. Igual que en cualquier lenguaje de programación, la llamada también será diferente puesto que las funciones tienen que recoger ese valor.

Procedimientos

Podemos verlas partes más comunes con un ejemplo. Suponemos que tenemos una BD con una tabla artículos y queremos un procedimiento que incremente el precio de un artículo.

Ejemplo de procedimiento

Para llamar a este procedimiento:

sql> CALL incr_preu("pomes",0.25); -- CALL invoca un procedimiento
sql> SELECT @trobat -- para consultar variables globales

Es decir: el cliente de la BD solo tiene que saber el nombre del procedimiento y qué valores tiene que pasar como paraámetro. No tiene por qué saber ni el nombre de la tabla ni los nombres de los campos. Ahora bien, como hemos visto, si queremos consultar si el procedimiento ha encontrado o no el artículo, también tendremos que conocer el nombre de la variable global que modifica el procedimiento. Por eso será necesario que, en cuenta de un procedimiento, usar una función que devuelvo ese valor.

Para borrar un procedimiento:

sql> DROP PROCEDURE [IF EXISTS] nombre_procedimiento;

Funciones

Vemos las partes principales con otro ejemplo. En una BD de fútbol, donde tenemos en la tabla partidos los goles que ha marcado cada equipo en cada partido, queremos una función que nos devuelva cuántos partidos ha ganado cada equipo.

Ejemplo de función

Para hacer la llamada a una función no se hace con CALL. En vez de eso, como la función devuelve un valor, la llamada tiene que estar dentro de una expressión que recoge ese valor.

Por lo tanto, la llamada se puede hacer en diferentes contextos (siempre formando parte de una expressión). Ejemplos de llamadas:

sql> -- a) Mostrar los partidos ganados por el Barcelona
sql> SELECT guanyats("BAR");
sql>
sql> -- b) Guardar el valor en una variable para un uso posterior
sql> SET @ganados = guanyats("BAR");
sql> SELECT concat("El Barcelona ha ganado ", @ganados, " partidos.");
sql>
sql>  -- c) Mostrar, por ejemplo, los partidos ganados por cada jugador
sql>  --    y ponerlo enb una condición
sql> SELECT *, guanyats(jugadors.equip)
sql>    FROM jugadors
sql>    WHERE guanyats(jugadors.equip) > 10;
sql> -- d) Incrementar en 1000€ el sueldo de de un jugador por cada partido ganado
sql> UPDATE jugadors 
sql>    SET sou = sou + 1000 * guanyats(jugadors.equip);

Para borrar una función:

sql> DROP FUNCTION [IF EXISTS] nombre_funcion

Parámetros y variables.

Declaración de parámetros

En las funciones todos los parámetros son de entrada. Pero en la declaración de procedimientos podemos indicar cuales són de entrada, de salida o de entrada/salida. Para ello pondremos delante de cada parámetro:

  • IN: Parámetro de entrada. En el momento de llamar al procedimiento hay que darle un valor.
  • OUT: Parámetros de salida. Cuando llamamos al procedimiento este parámetro se debe asignar a una variable (sin ningún valor en concreto). El procedimiento asignará un valor a esta variable que podrá ser consultado posteriormente:
  • INOUT: Parámetro de entrada y salida.

Ejemplo de procedimiento con parámetros

** Nota ** El nombre de las variables en MySQL no son case sensitive. Es decir, se considera la misma variable: - preu - Preu - PREU

Declaración de variables locales

El ámbito visibilidad de una variable estará entre el BEGIN y END donde está declarada la variable.

Sintaxis:

DECLARE nomvar[, ...] tipo [DEFAULT valor];
Siempre se declaran justo a continuación del BEGIN de un PA. En cada sentencia DECLARE se pueden declarar varias variables peró siempre del mismo tipo y mismo valor inicial (DEFAULT). Si necesitamos diferentes tipo de variable o diferentes valores iniciales tendremos que poner diferentes sentencias DECLARE

Ejemplos:

sql> DECLARE edat INT; -- Si no ponemos valor por defecto será nul. 
sql> DECLARE preu, import INT DEFAULT 0;
sql> SET edat = 18; -- Asignamos un valor a la variable;

Declaración de variables globales

El ámbito de visibilidad es en cualquier lugar de la conexión. El valor de las variables globales se guardan mientras está activa la connexión a la BD.

  • Es la única forma de usar variables desde fuera de un PA.
  • No se aconseja su uso dentro de los PA.
  • No se declaran, simplemente se indica el nombre de la variable que queremos usar con el signo @ delante: @nombre_*var

Ejemplos: 

sql> SET @x = 10;
sql> CALL quantUsuaris(@quantitat);

Asignar valor a las variables

sql> -- SET (ya lo hemos visto antes):
sql> SET nom = “Pep”;
sql> SET naix = 1970;
sql> SET cog = “Garcia”, edat = naix + 45; SET @comptador = 0;
sql> SET @comptador = @comptador + 1
sql>
sql> --  SELECT... INTO:
sql> SELECT nom_alu, cog_alu INTO nom, cog 
sql>    FROM alumnes
sql>    WHERE codi = 5;
sql> -- Si la Select retorna más de una fila de resultatdos, dará error.
sql>
sql> -- Llamando a una función o procediment con parámetros definidos como OUT o INOUT:
sql> CALL quantUsuaris(@a);
sql> -- Suponiento que esos parámetre están definidos como OUT en el procediment

Ejercicios

Nota Utilizaremos la base de datos de fútbol.

Base de datos Employees

  1. Variables de usuario: Guarda en dos variables el usuario que tenga más conexiones activas y el nombre del usuario. 
    mysql> SET @nombre='';
mysql> SET @conexiones=0;
mysql>  
mysql> SELECT USER,count(*) as conexiones
mysql>    INTO @nombre,@conexiones
mysql>    FROM information_schema.PROCESSLIST
mysql>    GROUP BY USER
mysql>    ORDER BY conexiones DESC
mysql>    LIMIT 1;
mysql> 
mysql> select @nombre,@conexiones;
  2. Crea un bloque de instrucciones SQL en el que una variable de usuario de nombre 'usuario' tenga asignado el valor de un usuario (sin la parte host). Comprueba si dicho usuario tiene permiso de selección, inserción, borrado y actualización a nivel de todo Mysql. 
    mysql> SET @usuario = 'user1';
mysql> SET @select='';
mysql> SET @insert='';
mysql> SET @update='';
mysql> SET @delete='';
mysql>  
mysql> SELECT Select_priv, Insert_priv, Update_priv, Delete_priv
mysql>    INTO @select,@insert,@update,@delete
mysql>    FROM mysql.user
mysql>    WHERE User = @usuario
mysql>    LIMIT 1;
mysql>  
mysql> select @select,@insert,@update,@delete;

  3. Crea un procedimiento que muestre cuantos empleados pertenecen al 'staff' (este dato lo encontrás en la tabla titles). Recuerda darle un nombre correcto.Para obtener el número de empleados debes emplear la función count(*) en el select. Modifica el procedimiento anterior y añádele un comentario que describa lo que devuelve el procedimiento. Crea un usuario 'usuario1' y dale permisos de conexión y ejecución. Prueba que este usuario puede ejecutar el procedimiento.

  4. Crea un procedimiento que muestre el número de los empleados que estén trabajando en el departamento 'd005' desde 1990. En la creación del procedimiento utiliza la cláusula comment. Recuerda de darle un nombre correcto.

  5. Para obtener el número de empleados debes emplear la función count(*) en el select.
  6. Para obtener el año de una columna de tipo date debes de aplicar la función YEAR(columna_date). Dicha función devuelve el año en forma numérica.

  7. Fíjate que el enunciado indica 'estén trabajando'

  8. Muestra con una orden SQL el nombre de todos los procedimientos de la base de datos.

Aquí podemos ver un procedimiento que me dice los usuarios que tienen permiso de ejecución para la base de datos de employees.

mysql> CREATE PROCEDURE Procedimiento_getUsersExecute()
mysql> COMMENT 'Obtiene usuarios CON permiso de ejecución sobre la BD employees'
mysql> BEGIN
mysql> SELECT concat(User,'@',Host) as usuario
mysql>    FROM mysql.procs_priv
mysql>    WHERE Db = 'employees'
mysql>    ORDER BY usuario;
mysql> END
mysql> 
mysql> CALL Procedimiento_getUsersExecute();

  1. Variables locales:

  2. Crea un método de nombre department_getLast() que guarde en variables locales los datos del último departamento ordenado alfabeticamente. Después debe mostrar en una única columna de nombre 'datos_departamento' el departamento encontrado (pista: utiliza el método concat).

  3. Crea un método de nombre salarie_getMax() que guarde en variables locales los datos (número y salario) del empleando con mayor sueldo. Después debe mostrar en una única columna de nombre 'datos_salario' los datos del empleado encontrado (pista: utiliza el método concat) y recuerda que no debes asegurarte de no devolver más de una fila.

    • ayuda: 
       SELECT emp_no,salary
    FROM salaries
    WHERE salary = (SELECT max(salary) FROM salaries)

  4. Crea un método de nombre title_getYoung() que guarde en variables locales el número de empleado, el puesto y la fecha del empleado que menos tiempo lleva trabajando (sólo uno). Después debe mostrar el contenido de dichas variables en una única columna de nombre 'empleado_mas_joven' (pista: utiliza el método concat y curdate).

  5. ayuda:
      ```sql
  SELECT emp_no, title, from_date
       FROM titles
       WHERE to_date > CURDATE() and
            from_date = (SELECT MAX(from_date)
                           FROM titles
                           WHERE to_date > CURDATE())
  ```
    • Ampliación del anterior, que muestre el nombre del empleado.

Constructores de control de flujo

Sentencia IF

Sintaxis: 

     IF condición THEN sentencia
     [ ELSEIF condición THEN sentencia ] [ ELSE sentencia ]
     END IF;

Ejemplo:

     IF a<b and a<c THEN SET min = a; 
          ELSEIF b<c THEN SET min = b; 
          ELSE SET min = c;
     END IF

Sentencia CASE

Sintaxis:

     CASE case_value
          WHEN when_value THEN statement_list 
          [WHEN when_value THEN statement_list] ... 
          [ELSE statement_list]
     END CASE
     -- o también...
     CASE
     WHEN search_condition THEN statement_list 
          [WHEN search_condition THEN statement_list] ... 
          [ELSE statement_list]
     END CASE

Ejemplo:

     CASE mes
          WHEN 1 THEN set nom='gener'; 
          WHEN 2 THEN set nom='febrer';
          -- ...
          ELSE set nom='error';
     END CASE
     CASE
          WHEN nota>=0 and nota <5 THEN set text='insuficient'; 
          WHEN nota>=5 and nota <6 THEN set text='aprovat';
          -- ...
     END CASE;

Sentencia WHILE-DO

Sintaxis: 

     WHILE condición DO
          sentencias 
     END WHILE;

Ejemplo: 

     CREATE PROCEDURE exemple_while_do() 
     BEGIN
          DECLARE n INT DEFAULT 5; 
          WHILE n > 0 DO
               -- ...
               SET n = n -1; 
          END WHILE;
     END

Sentencia REPEAT-UNTIL

Sintaxis: 

     REPEAT 
          -- sentencies
     UNTIL condición 
     END REPEAT

Ejemplo: 

     CREATE PROCEDURE exemple_repeat_until() 
     BEGIN
          DECLARE n INT DEFAULT 5; 
          REPEAT
               -- ...
               SET n = n -1; 
          UNTIL n<=0 END REPEAT;
     END

Sentencia LOOP, LEAVE e ITERATE

Ejemlo:

     CREATE PROCEDURE exemple_loop(n INT) 
     BEGIN
          label1: LOOP
               SET n = n + 1; 
               IF n < 10 THEN
                    ITERATE label1; 
               END IF;
               LEAVE label1;
          END LOOP label1;
          SET @x = n; 
     END
Con LOOP creamos un bucle infinito: no ponemos condición al principio ni al final como en las estructuras WHILE o REPEAT. La condición está dentro del bucle: con el LEAVE saldremos del bucle y con ITERATE volveremos directamente al inicio. Estas dos sentencias se pueden poner dentro de otras estructuras de bucle. Estas tres sentencias LOOP, LEAVE e ITERATE no se recomiendan porque rmpen la idea de programación estructurada.

Ejercicios

  1. Crea procidimientos para insertar departamentos y empleados. Hay que pasar al procedimiento los datos necesarios, si el cósigo del departamento o empleado ya existe no debe hacer un UPDATE en vez de un INSERT. Realiza una prueba llamando con datos que existen y que no existen y comprueba su funcionamiento.
  2. Crea un procedimiento al que le pasemos un número entero. Debe calcular el factorial del número eniendo en cuenta que n! = n * (n-1) * (n-2) * ... * 1
  3. Crea un procesimiento al que le pasemos el nombre de una tabla y un número n. El procedimiento debe crear la tabla con dos campos (index y valor). Debe insertar n registros donde el index será: 1, 2, 3, .., n y valor serán números aleatorios.
  4. Crea una función que le pases un código de departamento y devuelva cuantos trabajadores tiene activos el departamento. Luego utilizando esta función:
    • Muestra un listado de departamentos y número de trabajadores ordenado por número de trebajadores.
    • Muestra el departamento (o departamentos) que tienen más trabajadores
    • Muestra el departamento (o departamentos) que tienen menos trabajadores
  5. Crea un procedimiento a partir del anterior al que le pasemos un número de trabajdores n. Que devuelva cuantos departamentos tienen más de n empleados.

Cursores

Los cursores se utilizan para recorrer uno a uno los registros del resultado de una sentencia SELECT para hacer alguna operación con esos valores. El modo de operación es:

     -- ...
     -- declaración del cursor
     DECLARE c_alumnes CURSOR FOR 
          SELECT camp1, camp2, camp3
          FROM alumnes
          WHERE curs = "1DAM"
     -- declaración del HANDLER (manejador error)
     DECLARE CONTINUE HANDLER FOR 
     NOT FOUND
     SET @acabat = TRUE;
     -- Recorrer los elementos
     OPEN c_alumnes; -- abrir el cursor
     -- Inicio del bucle
     REPEAT
          FETCH c_alumnes INTO var1, var2, var3
          -- realizamos operaciones con l as variables
     UNTIL @acabat
     CLOSE c_alumnes;
     -- ...
Ejemplo: 
CREATE PROCEDURE createEmailList (
         INOUT emailList varchar(4000))
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE emailAddress varchar(100) DEFAULT "";

    -- declare cursor for employee email
    DEClARE curEmail 
        CURSOR FOR 
            SELECT email FROM employees;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER FOR 
          NOT FOUND SET finished = 1;

    OPEN curEmail;

    REPEAT -- 
        FETCH curEmail INTO emailAddress;
        -- build email list
        SET emailList = CONCAT(emailAddress,";",emailList);
    UNTIL finished = 1 END REPEAT;
     -- close cursor
    CLOSE curEmail;
END

Ejercicios de cursores

  1. Crea la función listar_departamentos, que te devuelva una cadena con los nombres de los departamentos separados por ';'.
  2. Crea la función listar_trabajadores, le pasaremos un departamento y nos devolverá los nombres deparados por ';' de los trabajadores activos del deparmaneto.
  3. Crea una función salario_medio, le pasaremos un departamento y mediante cursores nos calculará la media de salario de los trabajadores activos del departamento.
  4. Crea una función salario_maximo, le pasaremos un departamento y mediante cursores nos calculará el salario máximo de los trabajadores activos del departamento.

Trigers

  • 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.

Crear Triguers

La orden sql que crea un trigger es CREATE TRIGGER Disponéis de varios ejemplos en este enlace.

Sintáxis trigger

  • 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.

Visualizar los triguers

  • 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 triguers

No es posible. Se deben borrar y volver a crear

Otros aspectos

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”)

DATE_FORMAT(date,format): Formatea una fecha (por ejemplo, poner el resultado de la forma:día-mes-año).

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';

Ejecicicios de ejemplo

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 se intenta 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á.

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     END IF;
24 
25     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
26         SIGNAL SQLSTATE '45000' SET message_text='No hay artistas para cuidar a animales';
27     END IF;
28     
29     INSERT INTO ANIMALES_ARTISTAS (nombre_animal,nif_artista)
30     VALUES (NEW.nombre, v_nifArtista);
31     
32 END $$
33 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`)
     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');
2 INSERT INTO `CIRCO`.`ATRACCION_DIA` (`nombre_atraccion`, `fecha`, `num_espectadores`, `ganancias`) VALUES ('El orangután', '2020-03-05', '100', '10000.00');
3 UPDATE `CIRCO`.`ATRACCION_DIA` SET `ganancias` = '35000.00' WHERE (`nombre_atraccion` = 'El orangután') and (`fecha` = '2020-03-02');
4 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

2 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

3 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');

2 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');
2 UPDATE `CIRCO`.`PISTAS` SET `aforo` = '150' WHERE (`nombre` = 'LATERAL3');
3 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');
2 DELETE FROM `CIRCO`.`PISTAS` WHERE (`nombre` = 'ESPECTACULAR');
3 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';

Ejercicios

Base de datos a utilizar employees

  1. Crear una tabla contador con dos campos nombre_tabla y número. Tendra un registro para la tabla de empleados y otro para departamentos. Cuando se inserte un nuevo departamento debe de sumar uno al contador de deparmatmentos y cuando se elimine restar uno. Lo mismo para empleados. La tabla debe inicializarse con el número total de empleados y departamentos.
  2. Controlar mediante trigger que no se pueda dar de alta un empleado en un departamento en el que ya haya estado alguna vez.
  3. Crearemos una tabla log donde controlaremos mediante triguers que usuarios crean, cambian o eliminan datos de las tablas de departamentos, empleados o dept_emp.