5.2.- Ejemplos de disparadores.

Ya hemos visto qué son los disparadores, los tipos que existen, cómo definirlos y algunas consideraciones a tener en cuenta a la hora de trabajar con ellos.

Ahora vamos a ver algunos ejemplos de su utilización con los que podremos comprobar la potencia que éstos nos ofrecen.

Ejercicio resuelto

Como un agente debe pertenecer a una familia o una oficina pero no puede pertenecer a una familia y a una oficina a la vez, deberemos implementar un disparador para llevar a cabo esta restricción que Oracle no nos permite definir desde el DDL.

Retroalimentación

Para este cometido definiremos un disparador de fila que saltará antes de que insertemos o actualicemos una fila en la tabla agentes, cuyo código podría ser el siguiente:

CREATE OR REPLACE TRIGGER integridad_agentes
BEFORE INSERT OR UPDATE ON agentes
FOR EACH ROW
BEGIN
     IF (:new.familia IS NULL and :new.oficina IS NULL) THEN -- Si los dos valores son nulos
          RAISE_APPLICATION_ERROR(-20201, 'Un agente no puede ser huérfano');-- Lanzauna excepción
     ELSIF (:new.familia IS NOT NULL and :new.oficina IS NOT NULL) THEN -- Si los dos tienen valores
          RAISE_APPLICATION_ERROR(-20202, 'Un agente no puede tener dos padres');
     END IF;
END;
/

Debes conocer

En la siguiente presentación podrás ver la descripción de la tabla USER_TRIGGERS, cómo se almacena un disparador en la base de datos, cómo se consulta su código y cómo se lanza su ejecución. Pruébalo en tu máquina para comprobar su funcionamiento.

Presentación sobre disparadores. Creación, consulta de su código y lanzamiento con una sentencia insert (odp - 296,81 KB)

Resumen textual alternativo

Ejercicio resuelto

Supongamos que tenemos una tabla de históricos para agentes que nos permita auditar las familias y oficinas por la que ha ido pasando un agente. La tabla tiene la fecha de inicio y la fecha de finalización del agente en esa familia u oficina, el identificador del agente, el nombre del agente, el nombre de la familia y el nombre de la oficina. Queremos hacer un disparador que inserte en esa tabla.

Retroalimentación

Para llevar a cabo esta tarea definiremos un disparador de fila que saltará después de insertar, actualizar o borrar una fila en la tabla agentes, cuyo código podría ser el siguiente:

CREATE OR REPLACE TRIGGER historico_agentes
AFTER INSERT OR UPDATE OR DELETE ON agentes
FOR EACH ROW
DECLARE
     oficina VARCHAR2(40);
     familia VARCHAR2(40);
     ahora DATE := sysdate;
BEGIN
     IF INSERTING THEN
          IF (:new.familia IS NOT NULL) THEN
               SELECT nombre INTO familia FROM familias WHERE identificador = :new.familia;
               oficina := NULL;
          ELSIF
               SELECT nombre INTO oficina FROM oficinas WHERE identificador = :new.oficina;
               familia := NULL;
          END IF;
          INSERT INTO histagentes VALUES (ahora, NULL, :new.identificador, :new.nombre, familia, oficina);
          COMMIT;
     ELSIF UPDATING THEN
          UPDATE histagentes SET fecha_hasta = ahora WHERE identificador = :old.identificador and fecha_hasta IS NULL;
          IF (:new.familia IS NOT NULL) THEN
               SELECT nombre INTO familia FROM familias WHERE identificador = :new.familia;
               oficina := NULL;
          ELSE
               SELECT nombre INTO oficina FROM oficinas WHERE identificador = :new.oficina;
               familia := NULL;
          END IF;
          INSERT INTO histagentes VALUES (ahora, NULL, :new.identificador, :new.identificador, familia, oficina);
          COMMIT;
     ELSE
          UPDATE histagentes SET fecha_hasta = ahora WHERE identificador = :old.identificador and fecha_hasta IS NULL;
          COMMIT;
     END IF;
END;
/

Ejercicio resuelto

Queremos realizar un disparador que no nos permita llevar a cabo operaciones con familias si no estamos en la jornada laboral.

Retroalimentación

CREATE OR REPLACE TRIGGER jornada_familias
BEFORE INSERT OR DELETE OR UPDATE ON familias
DECLARE
     ahora DATE := sysdate;
BEGIN
     IF (TO_CHAR(ahora, 'DY') = 'SAT' OR TO_CHAR(ahora, 'DY') = 'SUN') THEN
          RAISE_APPLICATION_ERROR(-20301, 'No podemos manipular familias en fines de semana');
     END IF;
     IF (TO_CHAR(ahora, 'HH24') < 8 OR TO_CHAR(ahora, 'HH24') > 18) THEN
          RAISE_APPLICATION_ERROR(-20302, 'No podemos manipular familias fuera del horario de trabajo');
     END IF;
END;
/