Basi della sintassi dei trigger: CREATE TRIGGER, WHEN, EXECUTE FUNCTION

Per creare un trigger in PostgreSQL, devi definire questi componenti:

• Nome del trigger.
• Tipo di evento (INSERT, UPDATE, DELETE).
• Momento di esecuzione (BEFORE o AFTER).
• Tabella a cui si riferisce.
• Funzione che verrà eseguita (in PL/pgSQL o in un altro linguaggio).

Ecco la struttura generale del comando:

CREATE TRIGGER nome_trigger
[BEFORE | AFTER] {INSERT | UPDATE | DELETE}
ON nome_tabella
[FOR EACH ROW | FOR EACH STATEMENT]
WHEN (condizione)
EXECUTE FUNCTION nome_funzione();

Esempio di trigger semplice

Creiamo una tabella base students e aggiungiamo un trigger che scatta dopo l'inserimento di una nuova riga.

Per prima cosa creiamo la tabella students

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Cosa succede qui? Abbiamo creato una tabella con i campi id, name e last_modified. Il campo last_modified conterrà la data e l'ora dell'ultima modifica della riga.

I trigger sono sempre collegati alle funzioni. Prima creiamo una funzione semplice che aggiornerà il campo last_modified ogni volta che viene aggiunta una riga:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Impostiamo la data e ora attuale nel campo last_modified
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Che magia è questa?

1. NEW — variabile speciale che contiene i nuovi valori della riga (per eventi INSERT o UPDATE).
2. CURRENT_TIMESTAMP — funzione che restituisce la data e ora attuale.
3. RETURN NEW — restituisce la riga modificata per il salvataggio successivo.

Ora creiamo il trigger vero e proprio:

CREATE TRIGGER set_last_modified
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Spiegazione:

• AFTER INSERT: il trigger scatta dopo l'inserimento di una nuova riga.
• ON students: il trigger si applica alla tabella students.
• FOR EACH ROW: il trigger scatta per ogni nuova riga.
• EXECUTE FUNCTION: indica quale funzione deve essere chiamata.

Verifica del trigger

Vediamo come funziona il nostro trigger:

INSERT INTO students (name) VALUES ('Alice');
SELECT * FROM students;

Vedrai un risultato simile a questo:

Il trigger ha aggiornato automaticamente il campo last_modified. Magia? No, solo PostgreSQL.

Uso delle condizioni con WHEN

A volte serve che il trigger venga eseguito solo in certe condizioni. Per questo si usa la parola chiave WHEN.

Vediamo un esempio in cui il trigger si attiva solo per certi valori.

Supponiamo di voler far scattare il trigger solo per studenti con il nome "Alice". Modifichiamo il nostro trigger:

CREATE OR REPLACE FUNCTION update_last_modified_condition()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_last_modified_condition
AFTER INSERT
ON students
FOR EACH ROW
WHEN (NEW.name = 'Alice')
EXECUTE FUNCTION update_last_modified_condition();

Ora il trigger aggiornerà il campo last_modified solo per studenti con il nome "Alice".

Proviamo:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');
SELECT * FROM students;

Risultato:

Nota bene: per lo studente "Bob" il campo last_modified è rimasto vuoto perché il trigger non è scattato.

Collegamento del trigger con la funzione: EXECUTE FUNCTION

La funzione è il cuore di ogni trigger. Un trigger non può esistere senza una funzione che ne definisce la logica. In PostgreSQL puoi scrivere funzioni in PL/pgSQL o in altri linguaggi supportati, come Python o C.

Facciamo un esempio usando una funzione PL/pgSQL.

Creiamo una funzione che registra le modifiche in una tabella separata audit_log.

Prima creiamo la tabella audit_log

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    student_id INTEGER NOT NULL,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

E ora – la funzione:

CREATE OR REPLACE FUNCTION log_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (operation, student_id)
    VALUES ('INSERT', NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Ora scriviamo il trigger:

CREATE TRIGGER log_student_insert
AFTER INSERT
ON students
FOR EACH ROW
EXECUTE FUNCTION log_insert();

E testiamo il tutto:

INSERT INTO students (name) VALUES ('Charlie');
SELECT * FROM audit_log;

Vedrai un risultato simile a questo:

Il trigger ha registrato automaticamente il log della nuova riga.

Errori e particolarità nel lavorare con i trigger

Errore: assenza della funzione. Se provi a creare un trigger senza funzione, PostgreSQL ti darà errore. Crea sempre la funzione prima del trigger.

Problemi di performance. Tanti trigger o funzioni complesse possono rallentare il database. Usali con attenzione.

Ricorsione. Se un trigger modifica la stessa tabella su cui scatta, può causare un ciclo infinito. Usa le condizioni WHEN per evitarlo.