Creazione di un semplice trigger per aggiornare i dati: AFTER INSERT

Immagina di avere una tabella dove vengono salvati i dati degli studenti. In questa tabella dobbiamo aggiornare automaticamente il campo last_modified (data dell’ultima modifica del record) ogni volta che aggiungiamo un nuovo studente. Questo campo è importante per tracciare le modifiche e gestire i dati.

Lo scenario è questo:

1. Quando viene aggiunto un nuovo record nella tabella, il campo last_modified prende automaticamente la data e l’ora attuali.
2. Usiamo un trigger AFTER INSERT, che scatta dopo che i dati sono stati inseriti con successo.

Creazione della funzione per il trigger

Per prima cosa dobbiamo creare una funzione in PL/pgSQL. Questa funzione aggiornerà il campo last_modified nella nostra tabella. La funzione è fondamentale per il funzionamento del trigger, perché il trigger indica solo cosa fare, ma tutta la logica la fa la funzione.

Quindi, partiamo dalla creazione della tabella:

-- Creiamo la tabella students
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    last_modified TIMESTAMP
);

Ora creiamo la funzione per aggiornare il campo last_modified:

-- Funzione per aggiornare last_modified
CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- Impostiamo l’ora attuale nel campo last_modified
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Vediamo cosa fa questa funzione:

• CREATE OR REPLACE FUNCTION update_last_modified() — creiamo una funzione chiamata update_last_modified. Se esiste già, viene sovrascritta.
• RETURNS TRIGGER — indichiamo che la funzione è fatta per essere usata con un trigger.
• NEW.last_modified := NOW(); — aggiorniamo il campo last_modified usando la funzione NOW(), che restituisce la data e l’ora attuali.
• RETURN NEW; — restituiamo il record aggiornato. Questo è obbligatorio per il trigger AFTER.

Creazione del trigger

Dopo aver creato la funzione, possiamo creare il trigger vero e proprio, collegandolo alla tabella students. Ecco come si fa:

-- Creiamo il trigger dopo l’inserimento del record
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

Ecco cosa succede qui:

• CREATE TRIGGER set_last_modified — creiamo un trigger chiamato set_last_modified.
• AFTER INSERT — il trigger scatta dopo che una riga è stata inserita con successo nella tabella.
• ON students — il trigger è collegato alla tabella students.
• FOR EACH ROW — il trigger viene eseguito per ogni nuova riga inserita nella tabella.
• EXECUTE FUNCTION update_last_modified(); — chiama la funzione che abbiamo creato prima.

Nota: il nome del trigger (set_last_modified) e della funzione (update_last_modified) può essere qualsiasi, ma è meglio seguire gli standard di naming per rendere il codice più chiaro.

Test del trigger

Vediamo come funziona il nostro trigger. Prima aggiungiamo qualche record nella tabella students:

-- Inseriamo dati nella tabella
INSERT INTO students (name, age) VALUES ('Ivan Ivanov', 20);
INSERT INTO students (name, age) VALUES ('Anna Petrova', 22);

Ora vediamo cosa c’è nella tabella:

-- Visualizziamo i dati nella tabella
SELECT * FROM students;

Il risultato atteso potrebbe essere qualcosa del genere:

Nota che il campo last_modified si è riempito automaticamente con la data e l’ora attuali per ogni record.

Errori che possono capitare

1. Errore: "relation does not exist" durante la creazione del trigger. Questo errore capita se la tabella students non è stata creata. Assicurati di aver creato la tabella prima di creare il trigger.
2. Errore di permessi. Se l’utente del database non ha i permessi per creare funzioni o trigger, il trigger non verrà creato. Controlla i privilegi dell’utente.
3. Manca la chiamata della funzione nel trigger. Se ti dimentichi di scrivere EXECUTE FUNCTION update_last_modified(), il trigger non potrà fare quello che serve.

Migliorare il trigger: aggiungere condizioni

Nella vita reale può essere utile limitare l’esecuzione del trigger a certe condizioni. Per esempio, se il campo age è minore di 18, non vogliamo aggiornare last_modified. Si può fare usando la clausola WHEN:

-- Creiamo un trigger con una condizione
CREATE TRIGGER set_last_modified
AFTER INSERT ON students
FOR EACH ROW
WHEN (NEW.age >= 18)
EXECUTE FUNCTION update_last_modified();

Ora il campo last_modified verrà aggiornato solo per gli studenti con età >= 18.

Applicazione pratica

Trigger come questo sono usati spesso nei progetti veri. Ecco qualche esempio:

• Aggiornamento automatico della data dell’ultima modifica del record (come abbiamo fatto).
• Tracciare i cambiamenti nel database e salvare queste modifiche in una tabella di log.
• Garantire l’integrità dei dati, per esempio controllando tabelle collegate prima di eseguire un’operazione.
• Audit dei dati per rispettare regole legali o aziendali.

Queste skill sono super utili se lavori con sistemi dove la precisione e l’affidabilità dei dati sono fondamentali, tipo sistemi bancari, gestionali di magazzino o CRM.

2

Compito

SQL SELF, livello 57, lezione 3

Bloccato

Creazione di una funzione e di un trigger per il riempimento automatico del campo data/ora di modifica

Creazione di una funzione e di un trigger per il riempimento automatico del campo data/ora di modifica

Apri