Disparadores y Funciones disparadoras (Trigger)

Un disparador ejecuta una función disparadora de forma automática cuando se produce un evento de mantenimineto de una tabla (operaciones INSERT - DELETE - UPDATE)

Se puede elegir ejecutar la función disparadora

Antes (BEFORE) del evento. La secuencia será Funcion-Operación
Después (AFTER) del evento. La secuencia será Operación-Función

Ejercicio 1:
Deseamos controlar los préstamos de una biblioteca de forma que cuando se intente poner una fecha de devolución anterior a la fecha de préstamo, se anote la incidencia en una tabla de errores:

tabla prestamos

cod_libro PK int
cod_lector PK int
fecha_prestamo PK date
fecha_devolucion date

tabla problemas

cuando PK timestamp
que character varying(200)

Función disparadora verifica()

BEGIN
if(new.fecha_devolucion < old.fecha_prestamo) then
    INSERT INTO problemas
        VALUES (now(),'Problema: '|| old.cod_lector || old.cod_libro || old.fecha_prestamo || new.fecha_devolucion);
else
    DELETE FROM prestamos
        WHERE cod_lector=old.cod_lector
        AND cod_libro=old.cod_libro
        AND fecha_prestamo=old.fecha_prestamo;
    INSERT INTO prestamos VALUES (old.cod_lector,old.cod_libro,old.fecha_prestamo,new.fecha_devolucion);
end if;
return null;
end;

Nota: No se puede hacer UPDATE dentro de un disparador para UPDATE, ya que crearía una referencia circular infinita:
UPDATE -> regla -> UPDATE -> regla ->...

Disparador d1:

Disparador de fila marcado.
BEFORE UPDATE.
Funcion disparadora: verifica

Ejercicio 2:
Deseamos que se controle la posibilidad de que se introduzcan datos de forma que se preste un libro ya prestado

Función disparadora: libro_prestado

DECLARE
    prestado int8;
BEGIN
    SELECT count(*) INTO prestado
    FROM prestamos
    WHERE cod_libro=new.cod_libro
    AND fecha_devolucion IS NULL;
    IF (prestado=2) THEN
        INSERT INTO problemas VALUES (now(),'Error prestando '||new.cod_libro||' a '||new.cod_lector);
        DELETE FROM prestamos WHERE cod_lector=new.cod_lector AND cod_libro=new.cod_libro AND fecha_prestamo=new.fecha_prestamo;
    END IF;
    return null;
END;

Disparador: control_prestamos

Disparador de fila: marcado
Funcion disparadora: libro_prestado
AFTER INSERT

Funcionamiento
Inserta el nuevo prestamo
Controla mediante libro_prestado
    Si comprueba que esta prestado 2 veces
        inserta un error en la tabla problemas
        borra el prestamo realizado
    Si no estaba prestado
        deja todo como estaba

Ejercicio 3:
Crear una funcion disparadora que imposibilite los borrados:

Funcion disparadora: protege_borrado

DECLARE
BEGIN
    return null;
END

Disparador d3

BEFORE DELETE
ROW LEVEL

Ejercicio 4:
Crear una función disparadora que permita tener datos calculados coherentes en una tabla matematicas (numero int PK, cuadrado int, cubo int, raiz real)

Funcion disparadora rellena_datos

DECLARE
BEGIN
    new.cuadrado=new.numero*new.numero;
    new.cubo=power(new.numero,3);
    new.raiz=sqrt(new.numero);
    return new;
END;

Nota: return new devuelve el registro. Como se hace antes (BEFORE), se entrega y se inserta o actualiza

Disparador d4

BEFORE INSERT
BEFORE UPDATE

Cuando se inserte o modifique un numero, recalcula automaticamente el resto de los campos

Ejercicio 5:
Refundir las 2 funciones de los ejercicios 3 y 4 en una sola que controle la acción que pretende realizar el usuario:

DECLARE
BEGIN
    IF(TG_OP='INSERT' OR TG_OP='UPDATE') THEN
        new.cuadrado=new.numero*new.numero;
        ...
        RETURN new;
    ELSEIF (TG_OP='DELETE') THEN
        RETIR null;
    END IF;
END;

Disparador

BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE