Triggers

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Introducció¶

Un trigger (disparador) és un bloc de codi que s'executa automàticament quan ocorre un esdeveniment en la base de dades. És a dir, no cal cridar-lo manualment: s'activa sol quan passa alguna cosa.

Normalment, l'esdeveniment és una operació INSERT, UPDATE o DELETE sobre una taula o, en el cas dels triggers INSTEAD OF, sobre una vista.

En PostgreSQL, un trigger sempre necessita una funció associada.

Primer cal definir una funció de tipus TRIGGER, que conté la lògica, i després associar-la a l'esdeveniment amb CREATE TRIGGER ... EXECUTE FUNCTION.

Triggers, procediments i funcions¶

Tant els triggers com els procediments i les funcions guarden codi en la base de dades. La diferència és qui decideix quan s'executa:

Trigger	Procediment o funció
S'executa automàticament	S'executa quan es crida
Està associat a una taula o esdeveniment	És independent
L'usuari no el pot executar directament	Es pot cridar manualment
Ideal per a validacions automàtiques	Ideal per a processos controlats

Per tant, si el codi s'ha d'executar sempre que passe alguna cosa, cal un trigger. En canvi, si es vol decidir quan s'executa, cal un procediment o una funció.

Moment d'execució¶

Un trigger s'executa en un moment concret respecte de l'operació INSERT, UPDATE o DELETE que el dispara:

BEFORE: abans de l'operació.
AFTER: després de l'operació.
INSTEAD OF: en lloc de l'operació. Només es pot utilitzar sobre vistes.

A més, es poden combinar diverses operacions en un mateix trigger, com BEFORE INSERT OR UPDATE.

També cal indicar quantes vegades s'executa:

FOR EACH ROW: una vegada per cada fila afectada.
FOR EACH STATEMENT: una sola vegada per sentència, independentment del nombre de files.

Estructura d'un trigger¶

Un trigger en PostgreSQL consta de dues parts:

Una funció de tipus TRIGGER, amb el codi que s'executarà.
La definició del trigger pròpiament dita, que associa la funció a una taula i a un esdeveniment.

CREATE OR REPLACE FUNCTION nom_funcio_trigger()
RETURNS TRIGGER AS $$
BEGIN
   -- codi PL/pgSQL
   RETURN NEW; -- o OLD, o NULL, segons el cas
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER nom_trigger
BEFORE INSERT ON taula
FOR EACH ROW
EXECUTE FUNCTION nom_funcio_trigger();

Així, la definició d'un trigger sempre respon a quatre preguntes: com es diu, quan s'executa, per quina operació s'executa i sobre quina taula actua.

El valor que retorna la funció té efecte sobre l'operació.

En un trigger BEFORE ROW, si es retorna NULL, s'omet l'operació per a eixa fila. Si es retorna NEW (possiblement modificat), l'operació continua amb eixos valors.
En un trigger AFTER ROW, el valor retornat s'ignora. Per conveni, es retorna NEW o NULL.

Documentació oficial de PostgreSQL

Registres `NEW` i `OLD`¶

NEW i OLD són registres especials que contenen la fila afectada per l'operació. Només es poden utilitzar dins de la funció d'un trigger FOR EACH ROW, accedint a cada columna amb la notació NEW.columna o OLD.columna:

OLD: conté els valors anteriors al canvi.
NEW: conté els valors posteriors al canvi.

Com que no totes les operacions tenen valors anteriors i posteriors, la disponibilitat de cada registre depén de l'operació:

Operació	`OLD`	`NEW`
`INSERT`	❌ No existeix	✅ Sí
`UPDATE`	✅ Sí	✅ Sí
`DELETE`	✅ Sí	❌ No existeix

Exercici: Trigger de validació

Crea un trigger que:

Es diga trg_validar_salari.
S'execute abans d'inserir o modificar.
Actue sobre employees.
S'execute per cada fila.
Llance un error si el salari és negatiu.

Solució

CREATE OR REPLACE FUNCTION validar_salari()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salari no vàlid';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validar_salari
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validar_salari();

Exercici: NEW i OLD

Crea un trigger que:

S'execute AFTER UPDATE sobre employees.
S'execute per cada fila.
Si el salari canvia, mostre per pantalla Salari antic: X - Salari nou: Y.

Solució

CREATE OR REPLACE FUNCTION mostrar_canvi_salari()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary != OLD.salary THEN
        RAISE NOTICE 'Salari antic: % - Salari nou: %', OLD.salary, NEW.salary;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_mostrar_canvi_salari
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION mostrar_canvi_salari();

Triggers i regles de negoci¶

Els triggers permeten aplicar regles de negoci directament en la base de dades. No obstant això, no totes les regles són bones candidates.

Regles adequades per a triggers¶

Com a regla pràctica, si una regla s'ha de complir sempre, independentment de l'aplicació que accedisca a les dades, pot ser una bona candidata a trigger. És el cas de:

Validacions simples: comprovar que les dades tenen valors vàlids.
Control d'integritat addicional: restriccions que no es poden expressar amb CHECK o claus foranes.
Auditoria: registrar els canvis en una taula d'historial.
Automatització senzilla: assignar dates, valors per defecte, etc.

Regles poc adequades per a triggers¶

En canvi, no és recomanable implementar amb triggers:

Processos molt complexos: són difícils de depurar si s'executen de manera oculta.
Lògica que afecta moltes taules: multiplica els efectes de cada operació.
Processos batch o càlculs pesats: alentirien cada operació sobre la taula.
Regles que canvien sovint: és més fàcil mantindre-les en l'aplicació.

Riscos dels triggers¶

Encara que siguen útils, els triggers tenen uns riscos que cal tindre en compte:

Efectes ocults: el codi s'executa automàticament i pot ser difícil saber què està passant.
Manteniment difícil: si una taula té molts triggers, entendre'n el comportament es complica.
Rendiment: si el trigger fa consultes o operacions costoses, alenteix totes les operacions que el disparen.

Bones pràctiques i errors comuns¶

Bones pràctiques¶

Per reduir els riscos anteriors, és convenient seguir aquestes pautes:

Moderació: no abusar dels triggers.
Documentació: explicar què fa cada trigger i per què.
Simplicitat: mantindre la lògica senzilla.
Noms descriptius: trg_... per al trigger i un nom clar per a la funció.
Rendiment: revisar-ne l'impacte abans de posar-lo en producció.

Errors freqüents¶

Els errors més habituals en treballar amb triggers són:

Recursivitat descontrolada: un trigger que, en modificar la seua pròpia taula, es torna a activar a si mateix indefinidament.
COMMIT o ROLLBACK dins d'una funció de trigger: no està permés, ja que una funció de trigger no pot controlar la transacció que l'ha invocada.
Encadenar triggers sense control: un trigger que provoca una acció que activa un altre trigger pot generar comportaments inesperats.

Consultar la pròpia taula des d'un trigger¶

Gràcies al seu model de control de concurrència (MVCC), en PostgreSQL un trigger FOR EACH ROW pot consultar la mateixa taula que està modificant sense restriccions especials. El risc real és la recursivitat sense control: si un trigger AFTER modifica la mateixa taula i eixa modificació torna a disparar el trigger, es pot entrar en un bucle infinit.

Per analitzar tots els canvis d'una sentència alhora, PostgreSQL ofereix les taules de transició (REFERENCING NEW TABLE AS ... OLD TABLE AS ...), disponibles des de la versió 10. Aquestes taules permeten treballar a nivell STATEMENT amb accés a totes les files afectades:

CREATE OR REPLACE FUNCTION comprovar_mitjana_lot()
RETURNS TRIGGER AS $$
DECLARE
    v_avg_salary NUMERIC;
BEGIN
    SELECT AVG(salary) INTO v_avg_salary FROM new_rows;

    IF v_avg_salary > 5000 THEN
        RAISE EXCEPTION 'Salari mitjà del lot massa alt';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_avg_salary
AFTER UPDATE OF salary ON employees
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION comprovar_mitjana_lot();

Resum¶

Un trigger és una ferramenta potent, però s'ha d'utilitzar amb moderació. És adequat quan una regla senzilla s'ha de complir sempre o quan cal una auditoria o una automatització transparent (vegeu Regles adequades per a triggers). En canvi, quan la lògica és complexa, afecta el rendiment o és millor que l'execució siga visible i explícita, és preferible un procediment o una funció.

Si tot és automàtic, després és difícil d'entendre.

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.