Automatizzare le attività con i trigger

Immagina di essere l’admin di un grande database. All’improvviso qualcuno cancella delle info importanti da una tabella e tutti urlano: "Chi l’ha fatto?!". Per evitare queste scene, i database ti permettono di registrare le modifiche e vedere cosa è successo ai dati. Tutto questo si fa con logging e audit.

• Logging delle modifiche ti permette di salvare la storia di quello che è successo: quale record è cambiato, come e quando.
• Audit dei dati serve per controlli più approfonditi, registrando non solo le modifiche ma anche info sull’utente che le ha fatte.

Ora che hai capito il "perché", impariamo il "come".

Creare una tabella per i log

Prima di impostare i trigger, ci serve una tabella dove salvare i log delle modifiche. Ecco un esempio:

-- Creiamo una tabella per il logging delle modifiche
CREATE TABLE change_logs (
    log_id SERIAL PRIMARY KEY,       -- Identificatore unico del record
    table_name TEXT NOT NULL,        -- Nome della tabella dove è avvenuta la modifica
    operation TEXT NOT NULL,         -- Tipo di operazione: INSERT, UPDATE, DELETE
    change_time TIMESTAMP DEFAULT NOW(), -- Momento della modifica
    old_data JSONB,                  -- Dati prima della modifica (per UPDATE/DELETE)
    new_data JSONB                   -- Dati dopo la modifica (per INSERT/UPDATE)
);

Cosa succede qui?

1. log_id — identificatore unico per ogni record di log.
2. table_name — registriamo quale tabella è stata modificata.
3. operation — tipo di operazione: INSERT, UPDATE o DELETE.
4. change_time — salva il momento esatto della modifica.
5. old_data e new_data — dati prima e dopo la modifica in formato JSON.

Logging delle modifiche con un trigger

Ora che abbiamo la tabella dei log, creiamo un trigger per una delle tabelle, ad esempio students. Registrerà tutte le modifiche: aggiunta di nuovi studenti, aggiornamenti o cancellazioni. Ecco cosa facciamo:

1. Scriviamo una funzione in PL/pgSQL che aggiunge record nella tabella dei log.
2. Creiamo un trigger sulla tabella students.

La funzione riceverà info sull’operazione (INSERT, UPDATE, DELETE) e sui dati cambiati (OLD e NEW).

-- Funzione per registrare le modifiche nella tabella dei log
CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Logging dell’operazione INSERT
    IF TG_OP = 'INSERT' THEN
        INSERT INTO change_logs (table_name, operation, new_data)
        VALUES ('students', 'INSERT', row_to_json(NEW));

    -- Logging dell’operazione DELETE
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO change_logs (table_name, operation, old_data)
        VALUES ('students', 'DELETE', row_to_json(OLD));

    -- Logging dell’operazione UPDATE
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO change_logs (table_name, operation, old_data, new_data)
        VALUES ('students', 'UPDATE', row_to_json(OLD), row_to_json(NEW));
    END IF;

    RETURN NULL; -- Ritorniamo NULL perché è un trigger AFTER
END;
$$ LANGUAGE plpgsql;

Qui:

• TG_OP — variabile speciale che contiene l’operazione attuale: INSERT, UPDATE, DELETE.
• row_to_json(OLD) e row_to_json(NEW) — convertono la riga in formato JSON per salvarla facilmente.
• RETURN NULL — visto che è un trigger AFTER, non deve restituire dati modificati.

Ora colleghiamo la nostra funzione alla tabella students.

-- Creiamo un trigger per il logging delle modifiche
CREATE TRIGGER students_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION log_student_changes();

Cosa succede qui?

• AFTER INSERT OR UPDATE OR DELETE — il trigger scatta dopo le operazioni indicate sulla tabella students.
• FOR EACH ROW — il trigger si esegue per ogni riga modificata.
• EXECUTE FUNCTION log_student_changes() — chiama la nostra funzione di logging.

Testiamo il trigger

È il momento di vedere se il nostro trigger funziona.

1. Inserimento di un nuovo record

INSERT INTO students (name, age, grade)
VALUES ('Otto Lin', 20, 'A');

Vediamo cosa è stato scritto nella tabella dei log:

SELECT * FROM change_logs;

Esempio di risultato:

2. Aggiornamento di un record

UPDATE students
SET grade = 'B'
WHERE name = 'Otto Lin';

Controlliamo di nuovo la tabella dei log:

SELECT * FROM change_logs ORDER BY change_time DESC;

Risultato:

3. Cancellazione di un record

DELETE FROM students
WHERE name = 'Otto Lin';

E ancora, controlliamo il log:

Esempi di utilizzo reale

1. Logging delle operazioni su tabelle critiche: ad esempio, una tabella con conti bancari richiede il logging di tutte le modifiche per prevenire frodi.
2. Audit di sistema: puoi salvare i record per la compliance normativa o per analizzare l’attività degli utenti.
3. Recupero dei dati: se qualcuno cancella dati per sbaglio, puoi recuperarli dalla tabella dei log.

Particolarità e insidie

Quando usi i trigger per il logging, occhio alle performance. Se il trigger scatta troppo spesso, può aumentare il carico sul database. Quindi:

• Usa il logging solo sulle tabelle davvero importanti.
• Se i log diventano troppi, pensa a strategie di archiviazione.

I trigger sono come le corde di una chitarra: vanno regolati bene, ma se lo fai suonano alla grande, automatizzando le cose noiose e dandoti controllo sui dati.