Administración de Sistemas Gestores de Bases de Datos

Esta página es de solo lectura. Puedes ver la fuente pero no puedes cambiarla. Pregunta a tu administrador si crees que esto es incorrecto.
====== Disparadores (Triggers) y Eventos======
===== Disparadores =====

====Eventos de un Trigger====

Los disparadores o triggers son procedimientos de la Base de Datos que se ejecutan o activan cada vez que ocurren un evento determinado sobre una tabla determinada, según se haya indicado en el momento de su implementación.

Los eventos que se pueden asociar a la ejecución de un trigger son: 
  * //INSERT// 
  * //UPDATE// 
  * //DELETE//

==== Timing ====
También puede decidirse que se activen antes o después del evento en cuestión, utilizando las palabras reservadas ''BEFORE'' y ''AFTER''. 

Respecto al timing debemos tener algunas consideraciones:
  * Un trigger ''BEFORE'' se activa ante el simple intento de un ''INSERT'' o un ''UPDATE'', independientemente de si estas sentencias pueden lanzar un error.
  * Un trigger ''AFTER'' se ejecuta solamente si la operación que lo dispara se realiza correctamente.
  * Un error durante la ejecución de cualquier trigger, cancela automáticamente la operación que lo disparó. Es útil para controlar valores no deseados.

==== Palabras clave NEW y OLD ====
Las palabras ''NEW'' y ''OLD'' se emplean para referirse a las filas afectadas por el disparador, es decir, a las filas de la tabla sobre la que se activa, para referirse al estado de esa fila, antes (''OLD'') o después (''NEW'') de haber actuado el disparador. 

Las referencias NEW y OLD no están disponibles siempre, ya que si se borran o insertan registros no existe modificación:

^Evento del Trigger ^OLD^NEW^
|INSERT|NO|SI|
|UPDATE|SI|SI|
|DELETE|SI|NO|

Hay que tener en cuenta que cuando nos referimos a una columna precedida por ''OLD'', el acceso es de sólo lectura, por lo que se podrá hacer referencia a ella sólo para leerla. 
En el caso de las columnas precedidas por ''NEW'', su valor se podrá leer y también modificar con la instrucción ''SET'', siempre que el trigger se active antes de la operación (''BEFORE'').



==== Codificación ====

<code sql>
-- Crear trigger
CREATE TRIGGER nombre_trigger
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON nombre_tabla FOR EACH ROW
BEGIN
   ...
   ...
END;

-- Mostrar triggers
SHOW TRIGGERS;

-- Borrar trigger
DROP TRIGGER nombre_trigger;
</code>


Ejemplos:

<code sql>
-- Calcula automáticamente la edad de los usuarios
-- en el mismo momento en el que se dan de alta
-- a partir de la fecha de nacimiento que introduzca 
-- el usuario
CREATE TRIGGER nuevo_usuario BEFORE INSERT ON usuarios
FOR EACH ROW
BEGIN
    IF NEW.fecha_nacimiento IS NOT NULL THEN
        SET NEW.edad = YEAR(CURRENT_DATE()) - YEAR(NEW.fecha_nacimiento);
    END IF;
END;
</code>

<code sql>
-- Actualiza la fecha de última reserva de una pista
-- cada vez que ésta se reserva
CREATE TRIGGER anota_ultima_reserva AFTER INSERT ON reservas
FOR EACH ROW
BEGIN
    UPDATE pistas_abiertas
        SET fecha_ultima_reserva = CURRENT_TIMESTAMP()
        WHERE id_pista = NEW.id_pista;
END;
</code>

<code sql>
-- Registra una pista como pista clausurada al público cuando 
-- ésta se elimina de la Base de Datos
CREATE TRIGGER retira_pista AFTER DELETE ON pistas_abiertas
FOR EACH ROW
BEGIN
    INSERT INTO pistas_cerradas (id_pista, fecha_clausura, motivo)
        VALUES (OLD.id_pista, CURRENT_TIMESTAMP(), 'Eliminada');
END;
</code>

También podemos terminar un trigger mediante la sentencia ''LEAVE'', del mismo modo que en los procedimientos y funciones almacenadas.

==== Control de errores con Triggers ====
Es interesante conocer el //Timing// de actuación de los triggers ya que podemos modificar los valores en los casos en que el trigger actue antes (BEFORE) del evento.

En los triggers que se disparan ante un UPDATE o un INSERT en una tabla, podemos acceder a la información del registro NEW y modificar sus valores:

<code sql>
-- Modificar valores incorrectos
CREATE TRIGGER comprobar_nota_examen 
BEFORE INSERT ON examenes
FOR EACH ROW
BEGIN

IF NEW.nota < 0 THEN
   SET NEW.nota = 0
ELSEIF NEW.nota > 10 THEN
   SET NEW.nota = 10;
END IF;

END;

-- También podemos lanzar un código de error para cancelar la operación
CREATE TRIGGER control_edad_socio
BEFORE UPDATE ON socios
FOR EACH ROW
BEGIN

  IF NEW.edad < 18 THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Error en update: socio menor de edad';
  END IF;
END;

-- ó infringir una restricción para impedir la operación
-- En caso de que el campo edad sea NOT NULL

CREATE TRIGGER control_edad_socio
BEFORE INSERT ON socios
FOR EACH ROW
BEGIN

  IF NEW.edad < 18 THEN
      SET NEW.edad = NULL;
  END IF;
END;
</code>
=====Eventos=====
Un evento no es más que una tarea la cual se ejecuta de forma automática en un momento temporal previamente programado.

Permiten a los administradores de bases de datos programar ciertas tareas que queremos que se ejecuten de forma periódica o en un momento concreto.

====Implementación====

Lo primero es habilitar nuestro servidor para ejecutar //Eventos//:

<code sql>
SET GLOBAL event_scheduler = ON;
</code>

Para definir un evento que se ejecuta dentro de un minuto, y que inserta 3 registros en una tabla de registro: 

<code sql>
DELIMITER //

CREATE EVENT insertar_evento
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
BEGIN
 INSERT INTO registro VALUES ('Evento 1', NOW());
 INSERT INTO registro VALUES ('Evento 2', NOW());
 INSERT INTO registro VALUES ('Evento 3', NOW());
END //

DELIMITER ;

</code>

La temporalidad de los eventos se indica en la clausula ''ON SCHEDULE'', y puede ser:
  * ''AT'' -> Permite indicar un momento concreto en el que se ejecuta una sola vez.
  * ''EVERY'' -> Permite indicar una frecuencia con la que se ejecuta.
  * ''STARTS'' y ''ENDS'' -> Permite indicar cuando se activa o desactiva el evento.

Una vez que el evento se ha ejecutado se elimina automáticamente. Si queremos preservarlo debemos indicar la cláusula ''ON COMPLETION PRESERVE'' en su definición.

Són utiles para ejecutar procedimientos almacenados:
<code sql>
CREATE EVENT nombre_evento
ON SCHEDULE AT fecha_de_ejecución
DO
CALL procedimiento_almacenado();
</code>

Si queremos mostrar los eventos, eliminar un evento, detener temporalmente un evento o parar todos los eventos:
<code sql>
SHOW events;

DROP EVENT nombre_evento;

ALTER EVENT nombre_evento DISABLE|ENABLE;

SET GLOBAL event_scheduler = OFF;


</code>


==== Ejecutar eventos periodicamente ====
<code sql>
CREATE EVENT insertion_event
ON SCHEDULE EVERY 1 MINUTE STARTS '2018-07-07 18:30:00'
DO 
   INSERT INTO registro VALUES ('Evento 1', NOW());
</code>

==== Referencia ====
Las opciones de creación de eventos son las siguientes:

<code sql>
CREATE EVENT [IF NOT EXISTS] nombre_evento
    ON SCHEDULE temporalidad
    [ON COMPLETION [NOT] PRESERVE]
    
    DO operaciones_evento;

temporalidad:
    AT fecha-hora [+ INTERVAL intervalo ] ...  | EVERY intervalo
    [STARTS fecha-hora [+ INTERVAL intervalo ] ...]
    [ENDS fecha-hora [+ INTERVAL intervalo ] ...]

intervalo:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
</code>

----

(c) {{date> %Y}} Santiago Faci y Fernando Valdeón