Interazione dei trigger con le funzioni PL/pgSQL: OLD, NEW, TG_OP

I trigger in PostgreSQL non solo fanno partire funzioni in risposta a certe azioni, ma passano anche delle variabili magiche a queste funzioni. Grazie a queste variabili puoi sapere cosa c’era nei dati della tabella prima dell’operazione, cosa c’è dopo, e che operazione è stata fatta.

• OLD — contiene i dati vecchi della riga della tabella prima dell’operazione. Si usa nei trigger per UPDATE e DELETE, perché per INSERT non esiste niente di “vecchio”.
• NEW — contiene i dati nuovi della riga della tabella dopo l’operazione. Si usa nei trigger per INSERT e UPDATE.
• TG_OP — contiene info testuale sull’operazione attuale: INSERT, UPDATE o DELETE.

Tutte queste variabili sono disponibili automaticamente dentro la funzione collegata al trigger.

La teoria senza pratica è come SQL senza indici: lenta e triste. Quindi vediamo subito degli esempi pratici.

Uso di OLD per accedere ai dati vecchi

Immagina di avere una tabella students. E qualcuno ci cambia l’età di uno studente (magari si è confuso e pensa che lo studente abbia ancora 20 anni invece di 25).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL
);

Per tracciare che cambiamenti sono successi, creiamo una tabella di log:

CREATE TABLE student_changes (
    change_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    old_value INT,
    new_value INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ora andiamo avanti: creiamo una funzione che registra i cambiamenti. Qui ci serve proprio OLD:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Logghiamo il cambiamento dell’età
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (OLD.id, OLD.age, NEW.age);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

E ora creiamo il trigger:

CREATE TRIGGER student_age_update
AFTER UPDATE OF age ON students
FOR EACH ROW
WHEN (OLD.age IS DISTINCT FROM NEW.age) -- Esegui solo se l’età è cambiata
EXECUTE FUNCTION log_student_changes();

Aggiungiamo uno studente e poi cambiamo i suoi dati:

INSERT INTO students (name, age) VALUES ('Alisa', 20);

UPDATE students
SET age = 25
WHERE name = 'Alisa';

-- Controlliamo il log dei cambiamenti:
SELECT * FROM student_changes;

Vedrai che nella tabella di log è stato registrato il cambiamento: età da 20 a 25. Magia? No, OLD.

Uso di NEW per i dati nuovi

Ora immaginiamo di voler registrare automaticamente l’ID e il nome di un nuovo studente nella tabella di log quando lo aggiungiamo (sì, è un po’ da paranoici, ma a volte serve):

CREATE OR REPLACE FUNCTION log_new_student()
RETURNS TRIGGER AS $$
BEGIN
    -- Logghiamo i dati del nuovo studente
    INSERT INTO student_changes (student_id, old_value, new_value)
    VALUES (NEW.id, NULL, NEW.age); -- Nessun valore vecchio, perché è un INSERT
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_insert_log
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION log_new_student();

Ancora una volta, aggiungiamo un nuovo studente e controlliamo:

INSERT INTO students (name, age) VALUES ('Bob', 22);

-- Controlliamo il log:
SELECT * FROM student_changes;

Vedrai che nel log è apparso un nuovo studente. Questo sì che è prendersi cura dei dati a un altro livello!

Uso di TG_OP per capire il tipo di operazione

Ma se volessimo avere un trigger universale per il logging, che gestisce sia INSERT, sia UPDATE, sia DELETE? Qui ci viene in aiuto la variabile TG_OP.

Creiamo una funzione universale:

CREATE OR REPLACE FUNCTION log_all_operations()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (NEW.id, NULL, NEW.age);

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NEW.age);

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_changes (student_id, old_value, new_value)
        VALUES (OLD.id, OLD.age, NULL);

    END IF;

    RETURN NULL; -- Per un trigger AFTER su DELETE si ritorna NULL
END;
$$ LANGUAGE plpgsql;

Creiamo un trigger che scatta su tutte e tre le operazioni:

CREATE TRIGGER universal_student_log
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_all_operations();

Aggiungiamo, modifichiamo ed eliminiamo uno studente:

INSERT INTO students (name, age) VALUES ('Charlie', 30);
UPDATE students SET age = 31 WHERE name = 'Charlie';
DELETE FROM students WHERE name = 'Charlie';

-- Controlliamo il log:
SELECT * FROM student_changes;

Potrai vedere il log di tutte le operazioni — un solo trigger per gestirle tutte!

Errori tipici quando usi OLD, NEW, TG_OP

Lavorando con i trigger, puoi incappare in alcuni problemi comuni:

"Perché OLD non funziona su insert?" Questo è il comportamento standard: per INSERT non ci sono dati vecchi. Usa NEW.

"Cosa fare se NEW non funziona su delete?" Anche qui, è normale: per DELETE non ci sono dati nuovi. Usa OLD.

La logica del trigger causa ricorsione infinita. Controlla che il trigger non richiami se stesso per sbaglio. Per questo puoi mettere condizioni precise nel blocco WHEN o controllare TG_OP.

1

Sondaggio/quiz

Introduzione ai trigger, livello 57, lezione 4

Non disponibile

Introduzione ai trigger

Introduzione ai trigger

Apri