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

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

Ejemplos:

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

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

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

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:

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

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:

SET GLOBAL event_scheduler = ON;

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

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 ;

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:

CREATE EVENT nombre_evento
ON SCHEDULE AT fecha_de_ejecución
DO
CALL procedimiento_almacenado();

Si queremos mostrar los eventos, eliminar un evento, detener temporalmente un evento o parar todos los eventos:

SHOW events;
 
DROP EVENT nombre_evento;
 
ALTER EVENT nombre_evento DISABLE|ENABLE;
 
SET GLOBAL event_scheduler = OFF;

Ejecutar eventos periodicamente

CREATE EVENT insertion_event
ON SCHEDULE EVERY 1 MINUTE STARTS '2018-07-07 18:30:00'
DO 
   INSERT INTO registro VALUES ('Evento 1', NOW());

Referencia

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

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}