Desencadenadores (triggers)

Introducción

Se llama trigger (o disparador) al código que se ejecuta automáticamente cuando se realiza una determinada acción sobre la base de datos. El código se ejecuta independientemente de la aplicación que realizó dicha operación.

De esta forma tenemos tres tipos triggers:

♦ Triggers de tabla. Se trata de triggers que se disparan cuando ocurre una acción DML sobre una tabla.
♦ Triggers de vista. Se lanzan cuando ocurre una acción DML sobre una vista.
♦ Triggers de sistema. Se disparan cuando se produce un evento sobre la base de datos (conexión de un usuario, borrado de un objeto,…)

En este manual sólo se da cabida a los del primer y segundo tipo. Por lo que se dará por hecho en todo momento que nos referiremos siempre a ese tipo de triggers.

Los triggers se utilizan para:

♦ Ejecutar acciones relacionadas con la que dispara el trigger
♦ Centralizar operaciones globales
♦ Realizar tareas administrativas de forma automática
♦ Evitar errores
♦ Crear reglas de integridad complejas

El código que se lanza con el trigger es PL/SQL. No es conveniente realizar excesivos triggers, sólo los necesarios, de otro modo se ralentiza en exceso la base de datos.

Elementos de los triggers

Puesto que un trigger es un código que se dispara, al crearle se deben indicar las siguientes cosas:
(1) El evento que da lugar a la ejecución del trigger (INSERT, UPDATE o DELETE)
(2) Cuando se lanza el evento en relación a dicho evento (BEFORE (antes), AFTER (después) o INSTEAD OF (en lugar de))
(3) Las veces que el trigger se ejecuta (tipo de trigger: de instrucción o de fila)
(4) El cuerpo del trigger, es decir el código que ejecuta dicho trigger

Desencadenadores BEFORE y AFTER

 
En el apartado anterior se han indicado los posibles tiempos para que el trigger se ejecute. Éstos pueden ser:

♦ BEFORE. El código del trigger se ejecuta antes de ejecutar la instrucción DML que causó el lanzamiento del trigger.

♦ AFTER. El código del trigger se ejecuta después de haber ejecutado la instrucción DML que causó el lanzamiento del trigger.

♦ INSTEAD OF. El trigger sustituye a la operación DML Se utiliza para vistas que no admiten instrucciones DML.

Tipos de trigger
 
Hay dos tipos de trigger

♦ De instrucción. El cuerpo del trigger se ejecuta una sola vez por cada evento que lance el trigger. Esta es la opción por defecto. El código se ejecuta aunque la instrucción DML no genere resultados.

♦ De fila. El código se ejecuta una vez por cada fila afectada por el evento. Por ejemplo si hay una cláusula UPDATE que desencadena un trigger y dicho UPDATE actualiza 10 filas; si el trigger es de fila se ejecuta una vez por cada fila, si es de instrucción se ejecuta sólo una vez.

Sintaxis de la creación de triggers

triggers de instrucción
CREATE [OR REPLACE] TRIGGER nombreDeTrigger
cláusulaDeTiempo evento1 [OR evento2[,...]]
ON tabla
[DECLARE
declaraciones
]
BEGIN
cuerpo
[EXCEPTION
captura de excepciones
]
END;
La cláusula de tiempo es una de estas palabras: BEFORE o AFTER. Por su parte el evento tiene esta sintaxis:

{INSERT|UPDATE [OF columna1 [,columna2,…]]|DELETE}

Los eventos asocian el trigger al uso de una instrucción DML. En el caso de la instrucción UPDATE, el apartado OF hace que el trigger se ejecute sólo cuando se modifique la columna indicada (o columnas si se utiliza una lista de columnas

separada por comas). En la sintaxis del trigger, el apartado OR permite asociar más de un evento al trigger (se puede indicar INSERT OR UPDATE por ejemplo).

Ejemplo:

CREATE OR REPLACE TRIGGER ins_personal
BEFORE INSERT ON personal
BEGIN
IF(TO_CHAR(SYSDATE,’HH24’) NOT IN ('10','11','12')THEN RAISE_APPLICATION_ERROR(-20001,'Sólo se puede ' ||
'‘ añadir personal entre las 10 y las 12:59');
END IF;
END;

Este trigger impide que se puedan añadir registros a la tabla de personal si no estamos entre las 10 y las 13 horas.
triggers de fila

Sintaxis:

CREATE [OR REPLACE] TRIGGER nombreDeTrigger
cláusulaDeTiempo evento1 [OR evento2[,...]]
ON tabla
[REFERENCING {OLD AS nombreViejo | NEW AS nombreNuevo}]
FOR EACH ROW [WHEN condición]
[WHEN (condición)]
[declaraciones]
cuerpo

La diferencia con respecto a los triggers de instrucción está en la línea REFERENCING y en FOR EACH ROW. Ésta última es la que hace que el trigger sea de fila, es decir que se repita su ejecución por cada fila afectada en la tabla por la instrucción DML.
El apartado WHEN permite colocar una condición que deben de cumplir los registros para que el trigger se ejecute. Sólo se ejecuta el trigger para las filas que cumplan dicha condición.
El apartado REFERENCING es el que permite indicar un nombre para los valores antiguos y otro para los nuevos.

TABLAS NEW y OLD

Cuando se ejecutan instrucciones UPDATE, hay que tener en cuenta que se modifican valores antiguos (OLD) para cambiarles por valores nuevos (NEW). Las palabras NEW y OLD permiten acceder a los valores nuevos y antiguos respectivamente.
El apartado REFERENCING de la creación de triggers, permite asignar nombres a las palabras NEW y OLD (en la práctica no se suele utilizar esta posibilidad). Así NEW.nombre haría referencia al nuevo nombre que se asigna a una determinada tabla y OLD.nombre al viejo.

En el apartado de instrucciones del trigger (el BEGIN) hay que adelantar el símbolo “:” a las palabra NEW y OLD (serían :NEW.nombre y :OLD.nombre)
Imaginemos que deseamos hacer una auditoria sobre una tabla en la que tenemos un listado de las piezas mecánicas que fabrica una determinada empresa. Esa tabla es PIEZAS y contiene el tipo y el modelo de la pieza (los dos campos forman la clave de la tabla) y el precio de venta de la misma. Deseamos almacenar en otra tabla diferente los cambios de precio que realizamos a las piezas, para lo cual creamos la siguiente tabla:

CREATE TABLE PIEZAS_AUDIT(
precio_viejo NUMBER(11,4),
precio_nuevo NUMBER(11,4),
tipo VARCHAR2(2),
modelo NUMBER(2),
fecha DATE
);
Como queremos que la tabla se actualice automáticamente, creamos el siguiente

trigger:
CREATE OR REPLACE TRIGGER crear_audit_piezas
BEFORE UPDATE OF precio_venta
ON PIEZAS
FOR EACH ROW
WHEN (OLD.precio_venta<NEW.precio_venta)
BEGIN
INSERT INTO PIEZAS_AUDIT
VALUES(:OLD.precio_venta, :NEW.precio_vent
:OLD.tipo,:OLD.modelo,SYSDATE);
END;

Con este trigger cada vez que se modifiquen un registros de la tabla de piezas, siempre y cuando se esté incrementado el precio, se añade una nueva fila por registro modificado en la tabla de aditorías, observar el uso de NEW y de OLD y el uso de los dos puntos (:NEW y :OLD) en la sección ejecutable.
Cuando se añaden registros, los valores de OLD son todos nulos. Cuando se borran registros, son los valores de NEW los que se borran.

• IF INSERTING, IF UPDATING e IF DELETING

Son palabras que se utilizan para determinar la instrucción DML que se estaba realizando cuando se lanzó el trigger. Esto se utiliza en triggers que se lanza para varias operaciones (utilizando INSERT OR UPDATE por ejemplo). En ese caso se pueden utilizar sentencias IF seguidas de INSERTING, UPDATING o DELETING; éstas palabras devolverán TRUE si se estaba realizando dicha operación.
CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE OF campo1 ON tabla
FOR EACH ROW
BEGIN
IF DELETING THEN
instrucciones que se ejecutan si el trigger saltó por borrar filas
ELSIF INSERTING THEN
instrucciones que se ejecutan si el trigger saltó por insertar filas
ELSE
instrucciones que se ejecutan si el trigger saltó por modificar filas
END IF
END; 


Triggers de tipo INSTEAD OF

Hay un tipo de trigger especial que se llama INSTEAD OF y que sólo se utiliza con las vistas. Una vista es una consulta SELECT almacenada. En general sólo sirven para mostrar datos, pero podrían ser interesantes para actualizar, por ejemplo en esta declaración de vista:
CREATE VIEW
existenciasCompleta(tipo,modelo,precio,
almacen,cantidad) AS
SELECT p.tipo, p.modelo, p.precio_venta,
e.n_almacen, e.cantidad
FROM PIEZAS p, EXISTENCIAS e
WHERE p.tipo=e.tipo AND p.modelo=e.modelo
ORDER BY p.tipo,p.modelo,e.n_almacen;
Esta instrucción daría lugar a error
INSERT INTO existenciasCompleta
VALUES('ZA',3,4,3,200);
Indicando que esa operación no es válida en esa vista (al utilizar dos tablas). Esta situación la puede arreglar un trigger que inserte primero en la tabla de piezas (sólo si no se encuentra ya insertada esa pieza) y luego inserte en existencias.
Eso lo realiza el trigger de tipo INSTEAD OF, que sustituirá el INSERT original por el código indicado por el trigger:

CREATE OR REPLACE TRIGGER ins_piezas_exis
INSTEAD OF INSERT
ON existenciascompleta
BEGIN
INSERT INTO piezas(tipo,modelo,precio_venta)
VALUES(:NEW.tipo,:NEW.modelo,:NEW.precio);
INSERT INTO existencias(tipo,modelo,n_almacen,cantidad)
VALUES(:NEW.tipo,:NEW.modelo,
:NEW.almacen,:NEW.cantidad);
END;

Este trigger permite añadir a esa vista añadiendo los campos necesarios en las tablas relacionadas en la vista. Se podría modificar el trigger para permitir actualizar, eliminar o borrar datos directamente desde la vista y así cualquier desde cualquier acceso a la base de datos se utilizaría esa vista como si fuera una tabla más.