El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Un procediment cal cridar-lo. I n'hi ha prou que una aplicació, un becari amb psql o un procés d'importació escriguin directament a la taula per saltar-se'l sencer. Un disparador és el contrari: codi que s'executa sol, sense que ningú l'invoqui, en el moment exacte en què una fila s'insereix, es modifica o s'esborra. És l'única peça del curs capaç de garantir que una cosa passa sempre, escrigui qui escrigui.

Aquí es tanca la promesa de 09-02: les regles de negoci que un CHECK no pot expressar perquè necessiten consultar una altra taula. Veuràs l'anatomia completa, els quatre eixos que defineixen el comportament d'un disparador, les variables NEW i OLD amb el mecanisme que les fa útils —retornar NULL cancel·la l'operació—, els quatre casos canònics aplicats a BotigaVerda amb codi complet, i els INSTEAD OF que fan escrivibles les vistes de 10-01. I els seus perills, amb el mateix pes que els avantatges, perquè un disparador és lògica invisible: fa que un UPDATE faci coses que tu no vas escriure.

Contingut

Anatomia: funció de disparador + CREATE TRIGGER
Els quatre eixos
NEW, OLD, TG_OP i el valor de retorn
Cas 1: auditoria de canvis de preu
Cas 2: mantenir un total desnormalitzat
Cas 3: la regla que un CHECK no pot expressar
Cas 4: descompte automàtic d'estoc, i si convé
INSTEAD OF sobre vistes
Ordre de dispar i gestió
Els perills
Errors habituals i consells
Exercicis
Conclusió

Anatomia: funció de disparador + `CREATE TRIGGER`

A PostgreSQL un disparador són dos objectes, i aquesta separació desconcerta al principi:

Una funció que retorna el tipus especial TRIGGER i no rep paràmetres declarats.
Un CREATE TRIGGER que diu sobre quina taula, quan i amb quina granularitat s'executa aquesta funció.

CREATE OR REPLACE FUNCTION fn_trg_exemple() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    RAISE NOTICE 'Operació % sobre la taula %', TG_OP, TG_TABLE_NAME;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_exemple
    BEFORE INSERT OR UPDATE ON productes
    FOR EACH ROW
    EXECUTE FUNCTION fn_trg_exemple();

L'avantatge de separar-los és que una mateixa funció pot servir a diversos disparadors de diverses taules —d'aquí TG_TABLE_NAME—, i és exactament el que fa útil una funció d'auditoria genèrica.

(Tots els disparadors, funcions i taules d'aquesta lliçó són exemples: no formen part de l'esquema canònic de BotigaVerda de 01-06. La columna comandes.total del cas 2, en particular, no és canònica.)

Els quatre eixos

Eix	Valors	Què significa
Quan	`BEFORE` / `AFTER` / `INSTEAD OF`	Abans d'escriure (pot alterar o cancel·lar), després d'escriure (la fila ja existeix i té el seu `id`), o en lloc de l'operació (només sobre vistes)
Què	`INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`	Combinables amb `OR`. `UPDATE OF preu` ho restringeix a aquella columna
Granularitat	`FOR EACH ROW` / `FOR EACH STATEMENT`	Una vegada per fila afectada, o una vegada per sentència (encara que toqui un milió de files o cap)
Condició	`WHEN (condició)`	Filtre previ: el disparador ni s'activa si no es compleix

La combinació decideix gairebé tot:

Vols…	Fes servir
Validar o rebutjar una fila	`BEFORE ... FOR EACH ROW`
Modificar el valor que es desarà	`BEFORE ... FOR EACH ROW` (retornant `NEW` alterat)
Registrar, auditar o propagar a una altra taula	`AFTER ... FOR EACH ROW`
Un resum o una comprovació global després de la sentència	`AFTER ... FOR EACH STATEMENT`
Escriure a través d'una vista	`INSTEAD OF ... FOR EACH ROW`

BEFORE enfront d'AFTER en una frase: a BEFORE la fila encara no existeix, així que la pots canviar o cancel·lar, però el seu id autogenerat encara no està disponible en un INSERT; a AFTER ja existeix i el seu id és real, però retornar una cosa diferent de NULL no canvia res. Regla: valida i modifica a BEFORE; reacciona a AFTER.

I el WHEN no és només elegància: WHEN (OLD.preu IS DISTINCT FROM NEW.preu) evita executar la funció en els milions d'UPDATE que no toquen el preu. És l'optimització més barata que hi ha en un disparador.

`NEW`, `OLD`, `TG_OP` i el valor de retorn

Dins d'una funció de disparador FOR EACH ROW hi ha dues variables de tipus fila, i no sempre hi són totes dues:

Operació	`OLD`	`NEW`
`INSERT`	(no existeix)	La fila que s'inserirà
`UPDATE`	La fila abans del canvi	La fila després del canvi
`DELETE`	La fila que s'esborrarà	(no existeix)

Fer servir NEW en un DELETE o OLD en un INSERT dona record "new" is not assigned yet. D'aquí que una funció que atén diverses operacions hagi de preguntar amb TG_OP ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'). Hi ha més variables disponibles —TG_TABLE_NAME, TG_WHEN, TG_LEVEL, TG_ARGV[] amb els arguments del CREATE TRIGGER—, però amb aquestes dues es resol gairebé tot.

I ara el mecanisme que fa útils els disparadors: què significa el que retorna la funció.

Context	`RETURN NEW`	`RETURN NEW` modificat	`RETURN NULL`
`BEFORE ... FOR EACH ROW`	Continua amb normalitat	Es desa la fila alterada	L'operació es cancel·la en silenci
`AFTER ... FOR EACH ROW`	Indiferent	Indiferent	Indiferent
`FOR EACH STATEMENT`	Indiferent	Indiferent	Indiferent

Llegeix-ho a poc a poc, perquè són les dues capacitats que cap altra eina del curs no té: en un BEFORE ... FOR EACH ROW, retornar NULL cancel·la l'operació —la fila no s'insereix, l'UPDATE no s'aplica, i l'INSERT 0 0 és l'única pista— i retornar NEW amb camps canviats desa aquests canvis. En un DELETE, el que es retorna és OLD. En un AFTER, el valor de retorn s'ignora i per convenció s'escriu RETURN NULL.

Cancel·lar en silenci és perillós. Si la fila es rebutja per una regla de negoci, gairebé sempre és millor RAISE EXCEPTION amb un missatge clar que RETURN NULL: qui va escriure l'INSERT es mereix saber per què no ha passat res. Reserva el NULL per a descarts deliberats i documentats, com filtrar files brossa en una càrrega massiva.

Cas 1: auditoria de canvis de preu

L'ús més incontestable d'un disparador: registrar què va canviar, qui ho va canviar i quan. Cap aplicació no ho pot oblidar, perquè no depèn de cap aplicació.

CREATE TABLE auditoria_preus (            -- ⚠️ taula d'exemple, NO canònica
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producte_id INTEGER     NOT NULL,
    preu_ant    NUMERIC(10,2),
    preu_nou    NUMERIC(10,2),
    usuari      TEXT        NOT NULL,
    moment      TIMESTAMPTZ NOT NULL
);

CREATE OR REPLACE FUNCTION fn_audita_preu() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO auditoria_preus (producte_id, preu_ant, preu_nou, usuari, moment)
    VALUES (NEW.id, OLD.preu, NEW.preu, current_user, now());
    RETURN NULL;                       -- AFTER: el retorn s'ignora
END;
$$;

CREATE TRIGGER trg_productes_audita_preu
    AFTER UPDATE ON productes
    FOR EACH ROW
    WHEN (OLD.preu IS DISTINCT FROM NEW.preu)   -- només si el preu canvia de debò
    EXECUTE FUNCTION fn_audita_preu();

UPDATE productes SET preu  = 13.20 WHERE id = 1;    -- puja l'oli
UPDATE productes SET stock = 118   WHERE id = 1;    -- no toca el preu
SELECT producte_id, preu_ant, preu_nou, usuari FROM auditoria_preus;

producte_id	preu_ant	preu_nou	usuari
1	12.50	13.20	curs_sql

Una sola fila: el segon UPDATE no va disparar res gràcies al WHEN. Tres decisions que convé copiar d'aquest exemple: AFTER, perquè només s'audita el que va passar efectivament; IS DISTINCT FROM en lloc de <>, perquè amb NULL el <> no és cert i un canvi de NULL a un valor es perdria (04-03); i TIMESTAMPTZ amb now(), no l'hora que enviï el client.

Cas 2: mantenir un total desnormalitzat

01-06 va explicar per què comandes no té columna total: és un agregat que caldria mantenir sincronitzat. Quan el càlcul al vol i la vista de 10-01 ja no basten —un llistat de comandes que ordena i filtra per total sobre milions de files—, la sortida és desar-lo i mantenir-lo amb un disparador.

ALTER TABLE comandes ADD COLUMN total NUMERIC(10,2) NOT NULL DEFAULT 0;   -- ⚠️ NO canònica

CREATE OR REPLACE FUNCTION fn_recalcula_total_comanda() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_comanda_id INTEGER := COALESCE(NEW.comanda_id, OLD.comanda_id);
BEGIN
    UPDATE comandes AS co
    SET    total = COALESCE((SELECT ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)
                             FROM linies_comanda AS lc WHERE lc.comanda_id = v_comanda_id), 0)
    WHERE  co.id = v_comanda_id;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_linies_total
    AFTER INSERT OR UPDATE OR DELETE ON linies_comanda
    FOR EACH ROW EXECUTE FUNCTION fn_recalcula_total_comanda();

-- Emplenat inicial: el disparador només cobreix el futur
UPDATE comandes AS co
SET total = COALESCE((SELECT ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)
                      FROM linies_comanda AS lc WHERE lc.comanda_id = co.id), 0);

SELECT id, total FROM comandes WHERE id <= 6 ORDER BY id;

id	total
1	42.10
2	26.70
3	29.53
4	31.75
5	32.10
6	26.75

(6 primeres de 20 comandes; SELECT SUM(total) FROM comandes dona 727.95.) I si ara esborres una línia de la comanda 1, el seu total es recalcula sol.

Tres detalls que fan que funcioni i que s'obliden sempre: COALESCE(NEW.comanda_id, OLD.comanda_id), perquè en un DELETE no hi ha NEW; el COALESCE(..., 0) exterior, perquè en esborrar l'última línia el SUM retorna NULL i la columna és NOT NULL; i l'emplenat inicial, perquè un disparador acabat de crear no sap res del passat — és l'error número u en desnormalitzar.

I l'advertiment honest: això és el que es fa quan no queda més remei. Has canviat una lectura barata per una escriptura extra a cada INSERT, UPDATE i DELETE de línies, has creat una via perquè les dades es desincronitzin i has mogut una regla a un lloc invisible. Abans d'arribar aquí, prova amb una vista (10-01), amb un índex (mòdul 8) i amb una vista materialitzada.

Cas 3: la regla que un `CHECK` no pot expressar

Aquesta és la promesa de 09-02. La regla és "una ressenya només la pot escriure un client que hagi comprat aquell producte", i un CHECK no pot expressar-la: un CHECK només veu les columnes de la seva pròpia fila, i això exigeix consultar comandes i linies_comanda.

CREATE OR REPLACE FUNCTION fn_valida_ressenya() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM   comandes       AS co
        JOIN   linies_comanda AS lc ON lc.comanda_id = co.id
        WHERE  co.client_id   = NEW.client_id
          AND  lc.producte_id = NEW.producte_id
          AND  co.estat <> 'cancellat'
    ) THEN
        RAISE EXCEPTION 'El client % no ha comprat el producte %', NEW.client_id, NEW.producte_id
              USING ERRCODE = 'P0001',
                    HINT    = 'Només poden ressenyar els qui hagin comprat el producte';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_ressenyes_valida
    BEFORE INSERT OR UPDATE ON ressenyes
    FOR EACH ROW EXECUTE FUNCTION fn_valida_ressenya();

-- Hugo (client 14) no ha fet cap comanda
INSERT INTO ressenyes (producte_id, client_id, puntuacio, comentari, data)
VALUES (1, 14, 5, 'Genial', '2026-03-01');

ERROR:  El client 14 no ha comprat el producte 1
HINT:  Només poden ressenyar els qui hagin comprat el producte
CONTEXT:  PL/pgSQL function fn_valida_ressenya() line 12 at RAISE

I les 12 ressenyes existents continuen sent vàlides: totes corresponen a clients que van comprar aquell producte, així que el disparador no trenca res. Compara-ho amb l'exercici 3b de 01-06, on exactament aquest INSERT funcionava perquè cap restricció no ho impedia. Ara ja no.

Dues advertències imprescindibles. La primera: un disparador valida el que passa a partir d'ara, no el que ja hi és. En afegir una regla a una taula amb dades, comprova abans quantes files la incompleixen. La segona és més subtil: aquesta comprovació no és immune a la concurrència. Entre el SELECT de l'EXISTS i l'INSERT de la ressenya, una altra transacció podria cancel·lar la comanda; sota READ COMMITTED el disparador no ho veuria (09-04). Per a la majoria de les regles de negoci això és acceptable; per a un invariant que s'hagi de complir sempre, calen bloquejos explícits o SERIALIZABLE.

Cas 4: descompte automàtic d'estoc, i si convé

Tècnicament és el més senzill dels quatre:

CREATE OR REPLACE FUNCTION fn_descompta_stock() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE productes SET stock = stock - NEW.quantitat WHERE id = NEW.producte_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE productes SET stock = stock + OLD.quantitat WHERE id = OLD.producte_id;
    ELSE   -- UPDATE: retornar la quantitat antiga i descomptar la nova
        UPDATE productes SET stock = stock + OLD.quantitat WHERE id = OLD.producte_id;
        UPDATE productes SET stock = stock - NEW.quantitat WHERE id = NEW.producte_id;
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_linies_stock
    AFTER INSERT OR UPDATE OR DELETE ON linies_comanda
    FOR EACH ROW EXECUTE FUNCTION fn_descompta_stock();

Funciona, i el CHECK (stock >= 0) de productes impedeix que quedi negatiu: si algú intenta vendre més del que hi ha, l'UPDATE del disparador falla i tota la transacció es desfà, inclosa la línia que la va provocar.

Però convé? Aquí no hi ha una resposta única, i val la pena veure-la confrontada amb el sp_confirmar_comanda de 10-04:

	Disparador (aquí)	Procediment (10-04)
Es pot eludir?	No, escrigui qui escrigui a `linies_comanda`	Sí: n'hi ha prou amb fer l'`INSERT` a mà
Missatge d'error	Genèric: `violates check constraint "productes_stock_check"`	Clar: "Estoc insuficient de X: en queden 0 i se'n demanen 1"
Comprovació prèvia	No n'hi ha: es descobreix en fallar el `CHECK`	Sí, amb `FOR UPDATE` abans de decidir
Visibilitat	Invisible per a qui llegeix el codi de l'aplicació	Explícita
Càrrega massiva de 100.000 línies	Un `UPDATE` extra per línia	Es pot optimitzar en bloc
Coherència si algú corregeix un històric	Recalcula estoc, potser sense voler	No es toca

El criteri: si l'estoc és un invariant sagrat i hi ha diverses vies d'escriptura, disparador. Si hi ha una sola via d'entrada controlada i el missatge a l'usuari importa, procediment. I si tries el disparador, afegeix-hi un BEFORE que validi amb un missatge decent en lloc de deixar que l'error el doni el CHECK.

`INSTEAD OF` sobre vistes

10-01 va deixar pendent què fer amb les vistes que no són actualitzables automàticament —les que tenen JOIN, agregats o columnes calculades—. La resposta és un disparador INSTEAD OF, que substitueix l'operació pel que tu decideixis.

CREATE OR REPLACE FUNCTION fn_v_detall_insert() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari, descompte)
    VALUES (NEW.comanda_id, NEW.producte_id, NEW.quantitat,
            COALESCE(NEW.preu_unitari, (SELECT preu FROM productes WHERE id = NEW.producte_id)),
            COALESCE(NEW.descompte, 0));
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_v_detall_insert
    INSTEAD OF INSERT ON v_detall_vendes
    FOR EACH ROW EXECUTE FUNCTION fn_v_detall_insert();

Ara INSERT INTO v_detall_vendes (comanda_id, producte_id, quantitat) VALUES (1, 3, 2); funciona: la vista uneix quatre taules, però el disparador sap que només cal escriure a linies_comanda i d'on treure el preu. Tres regles: els INSTEAD OF només existeixen sobre vistes, sempre són FOR EACH ROW, i calen tres disparadors diferents —INSERT, UPDATE, DELETE— si vols que la vista sigui escrivible del tot. També existeix el sistema de regles (CREATE RULE), anterior i avui desaconsellat: reescriu la consulta abans d'executar-la, amb efectes sorprenents quan hi ha funcions volàtils pel mig.

Ordre de dispar i gestió

Quan diversos disparadors competeixen pel mateix moment sobre la mateixa taula, PostgreSQL els executa en ordre alfabètic per nom.

flowchart LR
    A["UPDATE productes<br/>SET preu = 13.20"] --> B["<b>BEFORE</b> ROW<br/>alfabètic"] --> C["s'escriu<br/>la fila"]
    C --> D["<b>AFTER</b> ROW<br/>alfabètic"] --> E["<b>AFTER</b> STATEMENT"]
    B -.->|"RETURN NULL"| F["cancel·lat"]

Que l'ordre depengui del nom és tan fràgil com sona: reanomenar trg_valida a trg_zvalida pot canviar el comportament del sistema sense que ningú toqui una línia de lògica. Si dos disparadors depenen de l'ordre entre ells, ajunta'ls en un de sol; si no en depenen, millor.

Tasca	Com
Llistar els d'una taula	`\d productes` a `psql`, o `SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid = 'productes'::regclass AND NOT tgisinternal;`
Veure'n la definició	`SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'trg_linies_stock';`
Desactivar / reactivar	`ALTER TABLE linies_comanda DISABLE TRIGGER trg_linies_stock;` … `ENABLE TRIGGER`
Desactivar-los tots	`ALTER TABLE linies_comanda DISABLE TRIGGER ALL;` (cal ser superusuari per als interns)
Esborrar	`DROP TRIGGER trg_linies_stock ON linies_comanda;`

DISABLE TRIGGER és imprescindible per a una càrrega massiva: importar un milió de línies amb el disparador d'estoc actiu són un milió d'UPDATE extra. Es desactiva, es carrega, es recalculen els agregats d'un cop i es reactiva. I ve amb un parany: mentre està desactivat, qualsevol escriptura normal també l'esquiva, així que fes-ho en una finestra controlada i no l'oblidis encès.

Anomena els disparadors amb prefix i patró: trg_<taula>_<què fa>. En una base amb dos-cents objectes, trobar per què un UPDATE fa una cosa estranya comença per poder llistar els disparadors d'aquella taula i entendre'n els noms.

Els perills

Es mereixen el mateix espai que els avantatges, perquè un disparador mal posat és de les coses més difícils de depurar que hi ha.

Lògica invisible. "L'UPDATE ha fet una cosa que jo no vaig escriure" és la frase que defineix el problema. El SQL de l'aplicació no esmenta el disparador enlloc; qui depura ha de sospitar que existeix. És la raó per la qual molts equips els limiten a auditoria i integritat, que són usos previsibles.
Cost per fila. FOR EACH ROW significa una execució per cada fila. Un UPDATE que toca 500.000 files executa 500.000 vegades la funció, amb les seves consultes a dins. El que en una fila triga 0,2 ms, en mig milió són 100 segons.
Cascades i recursió. Un disparador sobre linies_comanda que actualitza comandes pot activar un disparador sobre comandes que actualitza linies_comanda... i així fins a stack depth limit exceeded. Un disparador que modifica la seva pròpia taula és directament recursiu; es talla amb un WHEN que detecti que no hi ha res a fer, o amb pg_trigger_depth() = 0.
Difícils de provar. No es poden invocar aïlladament: cal provocar l'operació que els dispara, i les proves acaben sent d'integració.
Transaccionals, per bé i per mal. Un disparador corre dins de la transacció que el dispara: si falla, reverteix tot, inclosa l'operació original. És el que vols per a la integritat; és un desastre si a dins hi crides un servei extern o hi envies un correu (10-04, exercici 3).
Interacció amb COPY i amb les FK. COPY dispara els disparadors de fila, així que una importació pot ser molt més lenta del que s'esperava. I les claus foranes de PostgreSQL estan implementades com a disparadors interns, que no apareixen a \d però sí a pg_trigger amb tgisinternal = true.

Un disparador és la resposta correcta quan…	És un pedaç quan…
La regla s'ha de complir escrigui qui escrigui	Només hi ha una via d'escriptura: posa-ho allà i es veu
Cal auditar canvis de manera incontestable	Substitueix un `CHECK` o una FK que sí que ho podrien expressar
Un `CHECK` no pot perquè consulta una altra taula	Encadena efectes en cascada difícils de seguir
Manté una dada derivada que ja has decidit desnormalitzar	Es fa servir per apedaçar dades que l'aplicació envia malament
Es necessita `INSTEAD OF` sobre una vista	Conté lògica de negoci complexa: això és un procediment

Nota de dialecte: el concepte és universal, la sintaxi no. PostgreSQL separa funció i disparador, i és l'únic de la llista que ho fa. MySQL 8 posa el cos dins del CREATE TRIGGER, només admet FOR EACH ROW i no té INSTEAD OF ni disparadors de sentència. SQL Server treballa per sentència amb les pseudotaules INSERTED i DELETED en lloc de NEW/OLD, i sí que té INSTEAD OF. Oracle fa servir :NEW i :OLD amb dos punts i hi afegeix els compound triggers. SQLite els té, amb FOR EACH ROW obligatori i sense TRUNCATE.

Errors habituals i consells

Oblidar el RETURN en un BEFORE ... FOR EACH ROW. Caure al final de la funció retorna NULL, i això cancel·la l'operació en silenci: files que no s'insereixen sense cap error.
Fer servir NEW en un DELETE o OLD en un INSERT. record "new" is not assigned yet. Pregunta per TG_OP o fes servir COALESCE(NEW.x, OLD.x).
Crear un disparador de validació i no revisar les dades existents. Només s'aplica al futur; les files que ja incompleixen la regla es queden.
Desnormalitzar sense l'emplenat inicial. El disparador manté la columna a partir d'ara, però les 20 files anteriors es queden a 0.
Comparar amb <> en lloc d'IS DISTINCT FROM al WHEN. Amb NULL implicat, <> no és cert i el canvi no s'audita.
Posar un FOR EACH ROW on n'hi havia prou amb un FOR EACH STATEMENT, o ficar una consulta cara dins d'un disparador de fila. Multiplica pel nombre de files.
Fer crides externes des d'un disparador. Corre dins de la transacció: si aquesta es desfà, el correu ja s'ha enviat i no es pot desenviar.
Dependre de l'ordre alfabètic entre dos disparadors. És fràgil. Si l'ordre importa, unifica'ls.
Deixar DISABLE TRIGGER ALL posat. La càrrega massiva acaba i ningú no reactiva; a partir d'aquí les regles no s'apliquen i ningú no se n'assabenta fins mesos després.
Consell: prefix trg_ i patró trg_<taula>_<acció>. I documenta al CREATE TABLE, amb COMMENT ON TRIGGER, què fa i per què.
Consell: RAISE EXCEPTION amb missatge clar, no RETURN NULL. El silenci és el pitjor missatge d'error possible.
Consell: davant d'un comportament inexplicable, \d taula és la primera ordre. Els disparadors surten llistats allà, i moltes vegades l'explicació és en aquella línia.

Exercicis

Exercici 1

Dissenya l'auditoria completa de productes: una taula auditoria_productes que registri qualsevol INSERT, UPDATE o DELETE amb l'operació, la fila antiga i la nova en format text, l'usuari i el moment. (1) BEFORE o AFTER? ROW o STATEMENT? (2) Escriu la funció i el disparador fent servir TG_OP. (3) Com la faries genèrica per servir també a clients i comandes sense duplicar codi?

Exercici 2

Sobre el disparador de ressenyes de l'apartat 6, respon raonant i comprova: (1) Què passa si intentes inserir la ressenya de l'Hugo (client 14) sobre el producte 1? I si el client 1 ressenya el producte 1? (2) Per què el disparador és BEFORE i no AFTER? Funcionaria igual sent AFTER? (3) La comanda 6 està cancel·lada: si el seu client intentés ressenyar un producte que només va comprar en aquella comanda, passaria la validació? Localitza a la consulta la línia responsable.

Exercici 3

Un company ha posat aquest disparador sobre productes i ara cap UPDATE no acaba:

CREATE OR REPLACE FUNCTION fn_marca_revisio() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE productes SET data_alta = CURRENT_DATE WHERE id = NEW.id;
    RETURN NEW;
END; $$;

CREATE TRIGGER trg_productes_revisio AFTER UPDATE ON productes
    FOR EACH ROW EXECUTE FUNCTION fn_marca_revisio();

(1) Quin error dona i per què? (2) Reescriu-lo bé, sense UPDATE intern. (3) En quin altre cas, a més d'aquest, un disparador pot entrar en bucle?

Solucions

Solució 1

1. AFTER ... FOR EACH ROW. AFTER perquè només s'audita el que va passar de debò —a BEFORE, l'operació encara podria fallar per un CHECK o una FK i quedaria un registre d'una cosa que no va passar mai— i FOR EACH ROW perquè s'audita cada fila, no cada sentència. 2:

CREATE TABLE auditoria_productes (        -- ⚠️ taula d'exemple, NO canònica
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    taula TEXT, operacio TEXT, fila_id INTEGER,
    valor_ant TEXT, valor_nou TEXT, usuari TEXT, moment TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION fn_audita_generica() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO auditoria_productes (taula, operacio, fila_id, valor_ant, valor_nou, usuari, moment)
    VALUES (TG_TABLE_NAME, TG_OP,
            COALESCE(NEW.id, OLD.id),
            CASE WHEN TG_OP <> 'INSERT' THEN OLD::TEXT END,
            CASE WHEN TG_OP <> 'DELETE' THEN NEW::TEXT END,
            current_user, now());
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_productes_audita AFTER INSERT OR UPDATE OR DELETE ON productes
    FOR EACH ROW EXECUTE FUNCTION fn_audita_generica();

3. Ja ho és. La funció no esmenta productes enlloc: fa servir TG_TABLE_NAME per saber d'on ve i NEW::TEXT / OLD::TEXT per bolcar la fila sencera sigui quina sigui la seva estructura. N'hi ha prou amb crear un altre CREATE TRIGGER idèntic sobre clients i comandes. Els dos CASE són necessaris perquè OLD no existeix en un INSERT ni NEW en un DELETE. En un sistema real es desaria to_jsonb(NEW) en una columna JSONB en lloc de text, i així l'auditoria seria consultable clau a clau — que és 10-06.

Solució 2

1. La de l'Hugo falla amb ERROR: El client 14 no ha comprat el producte 1: no té cap comanda. La del client 1 sobre el producte 1 passa, perquè l'oli és a la línia 1 de la seva comanda 1. De fet aquella ressenya ja existeix: és la número 1 de les dotze.

2. És BEFORE perquè valida, i validar és rebutjar abans d'escriure. Sent AFTER també funcionaria —el RAISE EXCEPTION avortaria la transacció i la fila inserida es desfaria—, però s'hauria fet feina inútil: escriure la fila, actualitzar-ne els índexs i desfer-ho tot. BEFORE per validar, AFTER per reaccionar, i aquí a més BEFORE deixa oberta la porta a corregir NEW en lloc de rebutjar-lo.

3. No passaria, per la línia AND co.estat <> 'cancellat'. És una decisió de negoci deliberada: una comanda cancel·lada no és una compra, així que no dona dret a ressenyar. I és exactament el tipus de matís que només cap en un disparador: un CHECK no pot consultar comandes.estat, i una clau forana tampoc.

Solució 3

1. Dona ERROR: stack depth limit exceeded. El disparador s'activa AFTER UPDATE sobre productes i fa un UPDATE sobre productes, que el torna a activar, que torna a actualitzar… fins a esgotar la pila. És la recursió directa de l'apartat 10, i no hi ha cap protecció automàtica contra ella.

2. La forma correcta és no actualitzar res: modificar la fila que s'està escrivint es fa en un BEFORE, canviant NEW i retornant-lo.

CREATE OR REPLACE FUNCTION fn_marca_revisio() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.data_alta := CURRENT_DATE;    -- es desa amb la mateixa fila: sense UPDATE, sense recursió
    RETURN NEW;
END; $$;

CREATE TRIGGER trg_productes_revisio BEFORE UPDATE ON productes
    FOR EACH ROW EXECUTE FUNCTION fn_marca_revisio();

Aquest és el patró canònic de la columna actualitzat_el que porten gairebé totes les taules de producció, i la raó per la qual s'implementa amb BEFORE i no amb AFTER.

3. Amb cascades entre taules: A escriu a B, un disparador de B escriu a A, i el cicle es tanca sense que cap dels dos es modifiqui a si mateix. També amb ON DELETE CASCADE combinat amb disparadors d'esborrat, i amb dos disparadors que es reactiven mútuament a la mateixa taula. Les defenses: WHEN que talli quan ja no hi ha res a canviar (WHEN (OLD.total IS DISTINCT FROM NEW.total)), la comprovació IF pg_trigger_depth() > 1 THEN RETURN NULL; END IF;, o redissenyar perquè el cicle no existeixi.

Conclusió

Codi que s'executa sol, amb tot el que això implica:

Un disparador són dos objectes: una funció que retorna TRIGGER i un CREATE TRIGGER que diu sobre quina taula, quan i amb quina granularitat s'executa. La separació permet que una funció genèrica serveixi a diverses taules, amb TG_TABLE_NAME i TG_OP.
Els quatre eixos: quan (BEFORE valida i modifica, AFTER reacciona, INSTEAD OF substitueix), què (INSERT/UPDATE/DELETE/TRUNCATE), granularitat (FOR EACH ROW enfront de FOR EACH STATEMENT) i WHEN (condició), que evita disparar en va.
NEW i OLD no sempre hi són totes dues, i el valor de retorn és el mecanisme clau: en un BEFORE ... FOR EACH ROW, RETURN NULL cancel·la l'operació i RETURN NEW modificat l'altera. A AFTER s'ignora.
Els quatre casos de BotigaVerda: auditoria de canvis de preu amb WHEN (OLD.preu IS DISTINCT FROM NEW.preu) —l'ús més incontestable—; comandes.total desnormalitzat que suma 727,95 € i exigeix emplenat inicial; la regla de la ressenya que un CHECK no pot expressar perquè consulta una altra taula, tancant 09-02; i el descompte d'estoc, amb la comparació honesta enfront del sp_confirmar_comanda de 10-04.
Els INSTEAD OF fan escrivibles les vistes que 10-01 no podia actualitzar automàticament: només sobre vistes, sempre FOR EACH ROW, i un per operació.
L'ordre de dispar és alfabètic per nom, cosa que és fràgil: si dos disparadors depenen de l'ordre, unifica'ls. Es llisten amb \d i amb pg_trigger, s'apaguen amb ALTER TABLE ... DISABLE TRIGGER per a les càrregues massives i s'anomenen trg_<taula>_<acció>.
I els perills, amb el mateix pes: lògica invisible que sorprèn qui depura, cost per fila en càrregues massives, cascades i recursió fins a stack depth limit exceeded, dificultat per provar-los, i el fet que corren dins de la transacció que els dispara. Un disparador és la resposta correcta quan la regla s'ha de complir escrigui qui escrigui; és un pedaç quan substitueix un CHECK o amaga lògica de negoci que s'hauria de veure.

Amb vistes, CTE, funcions de finestra, procediments i disparadors, la caixa d'eines de SQL relacional està gairebé completa. Falta un cas que el model relacional porta malament per disseny: les dades la forma de les quals no és fixa. Un oli té acidesa i varietat; una crema, ingredients i tipus de pell; l'any que ve algú voldrà desar la petjada de carboni de cada producte. Afegir una columna per atribut és inviable, i muntar una taula de parells clau-valor és el remei clàssic i dolorós. A l'última lliçó del mòdul, JSON i dades semiestructurades, veuràs el tipus JSONB de PostgreSQL: com construir documents, com accedir-hi amb ->, ->> i JSONPath, com indexar-los amb GIN —la promesa pendent de 08-03—, com convertir-los de nou en files per continuar fent servir tot el SQL del curs, i —el més important— què no ha d'anar mai en un JSON.

Disparadors (triggers)

Contingut

Anatomia: funció de disparador + `CREATE TRIGGER`

Els quatre eixos

`NEW`, `OLD`, `TG_OP` i el valor de retorn

Cas 1: auditoria de canvis de preu

Cas 2: mantenir un total desnormalitzat

Cas 3: la regla que un `CHECK` no pot expressar

Cas 4: descompte automàtic d'estoc, i si convé

`INSTEAD OF` sobre vistes

Ordre de dispar i gestió

Els perills

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Disparadors (triggers)

Contingut

Anatomia: funció de disparador + CREATE TRIGGER

Els quatre eixos

NEW, OLD, TG_OP i el valor de retorn

Cas 1: auditoria de canvis de preu

Cas 2: mantenir un total desnormalitzat

Cas 3: la regla que un CHECK no pot expressar

Cas 4: descompte automàtic d'estoc, i si convé

INSTEAD OF sobre vistes

Ordre de dispar i gestió

Els perills

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Anatomia: funció de disparador + `CREATE TRIGGER`

`NEW`, `OLD`, `TG_OP` i el valor de retorn

Cas 3: la regla que un `CHECK` no pot expressar

`INSTEAD OF` sobre vistes