Audit delle tabelle con i trigger
Crei una traccia di audit con trigger AFTER INSERT/UPDATE/DELETE che scrivono in una tabella audit_log.
Audit delle tabelle con i trigger è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.
Perché eseguire l'audit?
I log di audit rispondono alla domanda «chi ha modificato cosa e quando». Sono richiesti per la conformità (indagini su HIPAA, SOX e diritto alla cancellazione previsto dal GDPR) e per le analisi forensi operative.
La tabella di audit
Un'unica tabella centrale registra ogni modifica:
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
user_name TEXT NOT NULL DEFAULT CURRENT_USER,
table_name TEXT NOT NULL,
action TEXT NOT NULL, -- INSERT, UPDATE, DELETE
row_id TEXT,
old_data JSONB,
new_data JSONB
);La funzione trigger di audit
Un'unica funzione, riutilizzabile in molte tabelle:
CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
VALUES (
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id::TEXT, OLD.id::TEXT),
CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;Collegamento alle tabelle
Utilizzi un trigger su AFTER INSERT/UPDATE/DELETE:
CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();to_jsonb(NEW)
Il trucco di to_jsonb acquisisce genericamente l'intera riga, senza codice specifico per ogni colonna. Funziona con qualsiasi tabella che abbia una colonna id.
Tracciamento dell'autore della modifica
Se l'applicazione viene eseguita con un unico utente del database, CURRENT_USER non è sufficiente. Trasmetta l'utente dell'applicazione tramite una GUC di sessione:
-- App sets:
SET LOCAL app.user_id = '42';
-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);Audit di colonne specifiche
Utilizzi la clausola WHEN del trigger per un audit selettivo:
CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();Tabelle di audit per ogni tabella
In alternativa, utilizzi una tabella di audit per ogni tabella reale (ad esempio users_audit) con lo stesso schema e i metadati di audit. È più facile da interrogare, ma richiede la manutenzione di una quantità maggiore di DDL.
Audit delle sole differenze
Memorizzi solo le colonne modificate:
INSERT INTO audit_log (table_name, action, changes)
VALUES (
TG_TABLE_NAME, TG_OP,
(SELECT jsonb_object_agg(key, value)
FROM jsonb_each(to_jsonb(NEW))
WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);Prestazioni della tabella di audit
La tabella di audit cresce rapidamente nei sistemi molto utilizzati. La partizioni in base al tempo:
CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');Compromesso sulle prestazioni
Ogni inserimento, aggiornamento o eliminazione ora scrive una seconda riga. Per le tabelle con un numero molto elevato di scritture, l'I/O può raddoppiare. Esegua test e monitoraggio, quindi decida se il compromesso vale la pena.
Quando NON utilizzare i trigger del database
Se ha bisogno dell'integrazione con un event bus, accodi gli eventi con NOTIFY o LISTEN oppure utilizzi strumenti di replica logica o CDC (Debezium) invece dei trigger.
Riepilogo
I trigger di audit sono il modo più semplice per acquisire la cronologia.
- Un'unica funzione generica con to_jsonb(NEW/OLD)
- La colleghi a ogni tabella sottoposta ad audit
- Partizioni le tabelle di audit in base al tempo
- Tracci esplicitamente l'autore dell'applicazione tramite GUC
Verifica rapida
All'interno di una funzione trigger di audit, come acquisisce genericamente la nuova riga in formato JSONB?
Domande Frequenti
La lezione «Audit delle tabelle con i trigger» è gratuita?
Sì — il testo completo di «Audit delle tabelle con i trigger» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.
Cosa imparerò in «Audit delle tabelle con i trigger»?
Crei una traccia di audit con trigger AFTER INSERT/UPDATE/DELETE che scrivono in una tabella audit_log. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Academy?
Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.
Quanto tempo richiede la lezione «Audit delle tabelle con i trigger»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Academy?
Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Anatomia dei trigger: BEFORE/AFTER, FOR EACH ROW
- Nozioni fondamentali sulle funzioni PL/pgSQL
- Blocchi DO e codice anonimo
- Audit delle tabelle con i trigger