Menu

Trigger PostgreSQL: CREATE TRIGGER con esempi

Un trigger esegue una funzione in automatico quando delle righe vengono inserite, aggiornate o eliminate. In PostgreSQL scrivi una funzione trigger che restituisce trigger, poi la colleghi a una tabella con CREATE TRIGGER.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

Un trigger fa sì che PostgreSQL chiami una funzione in automatico quando una tabella cambia. Scrivi prima la funzione trigger, dichiarata RETURNS trigger, poi collegala: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();. L'uso classico è tenere aggiornata una colonna updated_at:

È stato aggiornato solo il post 1, quindi solo lui ha un timestamp. NEW è la riga che sta per essere scritta; un trigger BEFORE può modificarla e deve restituirla. Puoi collegare la stessa funzione a ogni tabella che ha una colonna updated_at.

BEFORE vs AFTER vs INSTEAD OF

MomentoQuando giraUso tipico
BEFOREprima che la riga venga scrittamodificare o validare NEW, o saltare la riga
AFTERdopo che la riga è stata scrittascrivere in altre tabelle: log di audit, contatori
INSTEAD OFal posto della modifica, solo sulle visterendere scrivibile una vista

FOR EACH ROW esegue la funzione una volta per ogni riga coinvolta. FOR EACH STATEMENT (il predefinito) la esegue una volta per istruzione, anche se nessuna riga è cambiata. In un trigger di riga, NEW contiene la nuova riga (INSERT, UPDATE) e OLD quella precedente (UPDATE, DELETE). TG_OP indica l'operazione che lo ha fatto scattare e TG_TABLE_NAME la tabella.

Il valore restituito da un trigger di riga BEFORE decide cosa succede: restituisci NEW (modificata o no) per procedere, oppure NULL per saltare la riga senza errore. Il valore restituito da un trigger AFTER viene ignorato, quindi per convenzione restituisce NULL.

Scrivere un log di audit con un trigger AFTER

La tabella di audit registra due INSERT, un UPDATE da 10 a 12 e un DELETE del prodotto da 20. OLD è NULL in un insert e NEW è NULL in un delete, da qui il coalesce. Per salvare righe intere, metti to_jsonb(OLD) e to_jsonb(NEW) in una colonna jsonb.

Bloccare o saltare una modifica

Un trigger BEFORE può rifiutare una modifica con RAISE EXCEPTION, che interrompe l'istruzione, oppure saltare una riga restituendo NULL:

Il DELETE ha rimosso la bozza e ha tenuto in silenzio la fattura pagata. Un trigger BEFORE DELETE restituisce OLD per lasciar procedere l'eliminazione. Per far fallire invece l'intera istruzione, sostituisci RETURN NULL con RAISE EXCEPTION 'invoice % is paid', OLD.id;. Un errore è più facile da capire di una riga che resta lì senza dire niente.

Scattare solo quando cambia una colonna

UPDATE OF column limita un trigger agli update che impostano quella colonna. Una condizione WHEN salta la chiamata della funzione quando è falsa. IS DISTINCT FROM tratta NULL come un valore, quindi intercetta anche i cambi verso o da NULL:

Il primo update imposta lo stesso prezzo, quindi il WHEN è falso e il contatore resta a 0. Il secondo lo cambia, e price_changed finisce a 1.

Elencare, disabilitare ed eliminare i trigger

-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;

ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;

In psql, \d products elenca i trigger di una tabella. CREATE OR REPLACE TRIGGER esiste da PostgreSQL 14, ed EXECUTE FUNCTION da PostgreSQL 11; il codice più vecchio scrive EXECUTE PROCEDURE, che funziona ancora. Un trigger che scrive nella sua stessa tabella può farsi scattare di nuovo, quindi proteggiti dai cicli (pg_trigger_depth() restituisce il livello di annidamento). Il capitolo sulle funzioni trigger elenca tutte le variabili TG_, e funzioni e procedure copre PL/pgSQL.

Domande frequenti

Un trigger PostgreSQL può funzionare senza una funzione?

No. CREATE TRIGGER chiama sempre una funzione dichiarata RETURNS trigger. Scrivi prima la funzione, di solito in PL/pgSQL, poi collegala.

In che ordine scattano i trigger sulla stessa tabella?

I trigger dello stesso tipo sullo stesso evento scattano in ordine alfabetico di nome. Se l'ordine conta, metti come prefisso dei nomi numeri o lettere.

Un errore in un trigger annulla l'istruzione?

Sì. Un errore sollevato in un trigger, per esempio con RAISE EXCEPTION, interrompe l'istruzione che lo ha fatto scattare, e l'intera transazione a meno che tu non lo intercetti con un savepoint.

I trigger scattano con TRUNCATE?

I trigger DELETE a livello di riga no. Per reagire, crea un trigger a livello di istruzione con BEFORE TRUNCATE o AFTER TRUNCATE.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia