Trigger per aggiornare automaticamente il campo last_modified quando una riga viene modificata

Immagina di sviluppare un'app per gestire studenti e corsi, e hai una tabella students. In questa tabella c'è il campo last_modified, che dovrebbe aggiornarsi in automatico ogni volta che modifichi i dati di una riga (tipo quando cambi il nome dello studente o la sua età).

Invece di scrivere manualmente l'update di last_modified in ogni query SQL, creiamo un trigger che lo fa per noi.

Struttura della tabella students

Per cominciare, creiamo la tabella students che useremo nell'esempio. Questa tabella contiene le info base sugli studenti:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Identificatore unico dello studente
    name VARCHAR(100) NOT NULL,   -- Nome dello studente
    age INT,                      -- Età dello studente
    last_modified TIMESTAMP NOT NULL DEFAULT NOW() -- Ora dell'ultima modifica
);

• Il campo last_modified viene inizialmente riempito con l'orario attuale (NOW()) quando crei la riga.
• Questo campo si aggiornerà in automatico quando cambi i dati dello studente.

Inseriamo qualche dato di test nella tabella:

INSERT INTO students (name, age)
VALUES 
    ('Otto Lin', 20),
    ('Maria Chi', 22),
    ('Alex Song', 19);

Ora i dati nella tabella sono così:

Creazione della funzione per aggiornare last_modified

La funzione in PL/pgSQL verrà usata dal trigger per aggiornare il valore del campo last_modified. Verrà chiamata in automatico prima che una riga venga modificata.

Creiamo la funzione update_last_modified:

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Aggiorniamo il campo last_modified con l'orario attuale
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• NEW — è una variabile speciale che contiene i nuovi dati della riga (dopo la modifica).
• Impostiamo NEW.last_modified a NOW() (data e ora attuali).
• La funzione restituisce la variabile aggiornata NEW, che serve per far funzionare il trigger.

Creazione del trigger

Ora creiamo il trigger che chiamerà in automatico la funzione update_last_modified ogni volta che aggiorni una riga nella tabella students.

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Cosa succede qui:

• BEFORE UPDATE indica che il trigger parte prima dell'operazione di update.
• FOR EACH ROW vuol dire che il trigger scatta per ogni riga modificata.
• EXECUTE FUNCTION update_last_modified() dice di chiamare la funzione update_last_modified.

Test del trigger

Vediamo ora come funziona il nostro trigger. Selezioniamo i dati attuali dalla tabella students:

SELECT * FROM students;

Risultato:

Ora aggiorniamo l'età dello studente con student_id = 1:

UPDATE students
SET age = 21
WHERE student_id = 1;

Selezioniamo di nuovo i dati dalla tabella:

SELECT * FROM students;

Risultato atteso:

Nota: il campo last_modified per la riga con student_id = 1 è stato aggiornato all'orario attuale, mentre le altre righe sono rimaste uguali.

Estendere la logica del trigger

Supponiamo ora di voler aggiornare il campo last_modified solo se vengono modificati certi campi. Per esempio, se cambia solo il nome dello studente o la sua età, il trigger deve scattare, ma per altri cambiamenti no.

Per questo puoi aggiungere una condizione con l'operatore WHEN nella definizione del trigger.

Creiamo un nuovo trigger con condizione:

DROP TRIGGER IF EXISTS set_last_modified ON students;

CREATE TRIGGER set_last_modified
BEFORE UPDATE ON students
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name OR OLD.age IS DISTINCT FROM NEW.age)
EXECUTE FUNCTION update_last_modified();

Qui:

• La condizione WHEN controlla se i vecchi valori (OLD) sono diversi dai nuovi (NEW) per i campi name e age.
• Se nessuno di questi campi è cambiato, il trigger non parte.

Proviamo di nuovo ad aggiornare i dati nella tabella e testiamo la nuova logica.

Consigli sull'uso dei trigger

1. Non abusare dei trigger. Sono comodi, ma possono complicare la logica del database e rendere il debug più difficile.
2. Documenta sempre cosa fa un trigger e in quali casi viene usato.
3. Usa le condizioni WHEN per minimizzare chiamate involontarie del trigger.
4. Ricorda che i trigger possono influire sulle performance del database, soprattutto se la tabella ha tante righe.

Errori tipici con i trigger

Modifica sbagliata dei dati. Per esempio, ti dimentichi di impostare il valore per NEW e restituisci i dati originali senza cambiamenti.

Condizioni sbagliate. Tipo, ti dimentichi di aggiungere la condizione WHEN e il trigger scatta anche quando non serve.

Ricorsione. Se il trigger chiama una funzione che a sua volta richiama il trigger, rischi di creare un ciclo infinito. PostgreSQL ha una protezione contro la ricorsione, ma meglio evitare queste situazioni.

Questo esempio mostra come i trigger possono semplificare l'aggiornamento automatico dei dati. Nei progetti reali questa tecnica si usa spesso per tracciare le modifiche, mantenere l'integrità dei dati e automatizzare lavori noiosi.