Disparadores

o
triggers
Los componentes lógicos de una BDD vistos hasta ahora son insuficientes para implementar adecuadamente algunas de las
situaciones que se producen en el mundo real.

Supongamos que tenemos una tabla de existencias de productos de una organización determinada que tiene una regla de
negocio definida según la cual, cuando el stock de un producto queda por debajo de cincuenta unidades, hay que hacer un
pedido de cien unidades. Con lo visto hasta ahora se presentan dos soluciones convencionales para implementar esta situación:
• Añadir esta regla a todos los programas que actualizan las existencias de los productos.
• Hacer un programa adicional que haga un sondeo (en inglés, polling) periódico de la tabla para comprobar la regla.

La primera solución tiene diversos inconvenientes: la regla de negocio está escondida, distribuida y reproducida dentro del
código de los programas y, por lo tanto, es difícil de localizar y cambiar. Además, su eficacia o corrección depende del hecho de
que cada programador inserte la regla correctamente.

La segunda solución, aunque la regla está concentrada en un solo sitio (el programa de sondeo), presenta los inconvenientes
clásicos del sondeo: si se hace ocasionalmente, se puede perder el buen momento para reaccionar, y si, en cambio, se hace
muy a menudo, se pierde eficiencia.

La solución correcta es dotar al sistema de actividad. Se incorpora a la BDD un nuevo componente, los disparadores (en
inglés, triggers), que modelan la regla de negocio y se ejecutan de forma automática. Así el disparador que se
incorporaría a la BDD es el siguiente: "Cuando se modifiquen las existencias de un producto y queden por debajo de
cincuenta unidades, hay que pedir cien unidades nuevas". Ventajas: La regla de negocio está sólo en un sitio, la eficacia
no depende del programador y el disparador se ejecutará siempre que haga falta y sólo cuándo haga falta.
Disparadores o triggers
Los disparadores son unos componentes que se ejecutan
de una manera automática cuando se produce un evento
determinado.

Se dice que un disparador es una regla ECA (evento,

condición, acción): cuando se produce un evento
determinado, si se cumple una condición, se ejecuta una
acción.
 Cuándo se han de utilizar disparadores
• Implementación de una regla de negocio
• Mantenimiento automático de una tabla de auditoría de la actividad en la BDD. Se trata de registrar de una manera
automática los cambios que se hacen en una tabla determinada.
• Mantenimiento automático de columnas derivadas. El valor de una columna derivada se calcula a partir del valor de
otras columnas; posiblemente, de otras tablas. Cuando se modifican las columnas base, hay que recalcular de una
manera automática el valor de la columna derivada.
• Comprobación de restricciones de integridad no expresables en el sistema mediante CHECK o por medio de
restricciones de integridad referencial. Por ejemplo: “un sueldo no puede bajar“, que no se puede expresar con un
CHECK.
• Reparación automática de restricciones de integridad. Hay que distinguir entre comprobación y reparación de
restricciones de integridad. La semántica de la comprobación de una restricción de integridad se puede resumir así:
"Si una actualización infringe la restricción, hay que cancelar anormalmente o deshacer (en inglés, abort) la
actualización." La semántica de la reparación es la siguiente: "Si la restricción se infringe, hay que llevar a cabo
acciones compensatorias (nuevas actualizaciones) para que no se infrinja". Los SGBD normalmente implementan las
comprobaciones.
 Cuándo no se han de utilizar disparadores
Los triggers pueden ser útiles a la hora de implementar determinadas
situaciones del mundo real, pero no hay que abusar de ellos: no se
deberían utilizar disparadores en las situaciones en las que el sistema se
pudiera resolver con sus propios mecanismos, como con la integridad
referencial, o las restricciones CHECK,…
Qué SGBD tienen disparadores
Los encontramos tanto en los sistemas comerciales
Oracle
DB2
SQLServer
Informix

como en los de libre distribución

PostgreSQL
MySQL

El SQL estándar, desde el SQL:1999, define los disparadores como

componentes esenciales de las BD.
Disparadores o triggers
Es código PL/SQL asociado a una tabla y se ejecuta automáticamente
como reacción a una operación LMD (insert, update o delete) sobre
dicha tabla.
CREATE [OR REPLACE] TRIGGER nombre
[BEFORE | AFTER] evento
[OR [DELETE | INSERT| UPDATE {OF campos}]
ON tabla
[FOR EACH ROW [WHEN condición disparo]]
[DECLARE]
-- vbles
BEGIN
--instrucciones
[EXCEPTION]
--tratamiento de excepciones
END;
Disparadores o triggers
CREATE [OR REPLACE] TRIGGER nombre
[BEFORE | AFTER] [DELETE | INSERT| UPDATE {OF campos}]
[OR evento]
ON tabla
[FOR EACH ROW [WHEN condición disparo]]
[DECLARE]
-- vbles
BEGIN
--instrucciones
[EXCEPTION]
--tratamiento de excepciones
END;
Ejemplo
CREATE OR REPLACE TRIGGER TR1
AFTER INSERT ON Pedidos
FOR EACH ROW
DECLARE
-- local variables
BEGIN
dmbs_output.put_line('Nuevo pedido insertado');
END ;
 Disparadores o triggers
BEFORE: El código se ejecutará antes de la operación LMD
AFTER: El código se ejecutará justo después de la operación LMD
Si no se indica la operación LMD que dispara el trigger, se ejecutará con cualquier operación
LMD sobre la tabla.
Se pueden elegir una o varias tablas.
Se puede seleccionar que sea al modificar una determinada columna de la tabla.
Se pueden unir varias operaciones con el OR.
Disparador de FILA (FOR EACH ROW): se disparará cada vez que se realizan operaciones sobre
cada fila de la tabla.
Si no se indica FOR EACH ROW (disparador de ORDEN), solo se ejecutará una vez por operación
independientemente de las filas que se vean afectadas por la operación LMD.
WHEN indica que solo actuará sobre las filas que cumplan la condición.
No se pueden usar sentencias de control de transacciones
Ejemplos
CREATE OR REPLACE TRIGGER TR1
AFTER UPDATE ON Productos
FOR EACH ROW
DECLARE
-- local variables
BEGIN
dbms_output.put_line('Nuevo producto modificado');
END ;

CREATE OR REPLACE TRIGGER TR1

AFTER UPDATE ON Productos
DECLARE
-- local variables
BEGIN
dbms_output.put_line('Productos modificados');
END ;
 Orden de ejecución de los triggers
Si tenemos varios tipos de disparadores sobre una misma tabla, el orden de ejecución será:

• Triggers before de sentencia.

• Triggers before de fila.
• Triggers after de fila.
• Triggers after de sentencia.

Existe una vista del diccionario de datos con información sobre los disparadores:
USER_TRIGGERS;
Disparadores o triggers
Para acceder a la información del registro que se está procesando (insert, update o
delete) se utiliza:

:old :new
Son registros del mismo tipo que la tabla.

Si el disparador es lanzado al insertar, el valor antiguo no tendrá sentido y el valor nuevo

será la fila que estamos insertando.

Para un disparador lanzado al actualizar el valor antiguo contendrá la fila antes de actualizar
y el valor nuevo contendrá la fila que vamos actualizar.

Para un disparador lanzado al borrar sólo tendrá sentido el valor antiguo.

 Ejemplo
CREATE OR REPLACE TRIGGER TR_PRODUCTOS1
AFTER INSERT ON DetallePedidos
FOR EACH ROW
DECLARE
-- local variables
BEGIN
UPDATE Pedidos set preciototal = preciototal+(:new.cantidad * :new.preciounidad)
END ;
Disparadores o triggers
Como el trigger puede ser disparado por varias operaciones, se puede
conocer dentro del cuerpo del trigger qué operación lo lanzó:

If inserting|deleting|updating|updating(columna)
Ejemplo de trigger (jardinería)
CREATE OR REPLACE TRIGGER auditarPagos
BEFORE INSERT OR DELETE OR UPDATE OR UPDATE OF CANTIDAD ON pagos FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audiPagos VALUES (USER || ‘Introduce pago código: ‘|| :NEW.IDTRANSACCION ||’ Cantidad: ‘ ||
:NEW.CANTIDAD);

ELSIF DELETING THEN

INSERT INTO audiPagos VALUES (USER || ‘Borra pago código: ‘|| :OLD.IDTRANSACCION ||’ Cantidad: ‘ ||
:OLD.CANTIDAD);

ELSIF UPDATING(‘cantidad’) THEN

INSERT INTO audiPagos VALUES (USER || ‘ modifica la cantidad de la operación de código: ‘||
:OLD.IDTRANSACCION ||’ de ‘ || :OLD.CANTIDAD ||’ a ‘||:NEW.CANTIDAD);

ELSIF UPDATING THEN

INSERT INTO audiPagos VALUES (USER || ‘ modifica código: ‘|| :OLD.IDTRANSACCION ||’ Código actual: ‘ ||
:NEW.IDTRANSACCION);
END IF;
END;
Ejercicios triggers
1.Crea un trigger asociado a la tabla Pedidos, de manera que cuando
queramos borrar un registro de la tabla Pedidos elimine todos los
registros relacionados de la tabla DetallePedidos.

2. Crear una tabla estadisticasEmpleado con 2 campos,

codigoEmpleado y coste total de los pedidos de sus clientes.

Crea un trigger que cada vez que se da de alta un detalle de

pedido, actualice esta otra tabla.
Ejercicios triggers
1. Crea un trigger que cada vez que se da de alta un nuevo pedido, si está
gestionado por un empleado que no está en la tabla estadisticasempleado, lo dé
de alta con un totalpedido a 0.

2. Crea un trigger que mantenga actualizada esta tabla estadisticasempleado,

cuando se inserta, se modifica o se borra un detallepedido. (Si es necesario,
borra el trigger anterior creado para las inserciones, de modo que no haya
triggers redundantes)