Come implementare un meccanismo di audit o tracciamento delle modifiche (storia delle modifiche/logging) nelle tabelle SQL? Quali sono gli approcci, i loro pro e contro, e cosa è importante considerare nella progettazione di tale funzionalità?

Risposta

Il meccanismo di audit (log o storia delle modifiche) viene implementato per tracciare tutte le modifiche ai dati e garantire trasparenza. Ci sono diversi principali approcci:

1. Trigger di Audit (Audit Triggers): Trigger speciali AFTER/BEFORE INSERT/UPDATE/DELETE che salvano lo stato precedente e/o nuovo di una riga in una tabella di audit separata. Vantaggio — trasparenza per l'applicazione; svantaggio — possibile rallentamento delle operazioni DML.

2. Isolamento della storia in una tabella separata (History Table): Si applica la versioning dei record — al momento della modifica, la "vecchia" riga viene copiata in una tabella di history con una marca temporale. La tabella principale contiene solo i dati attuali.

3. Meccanismi incorporati (Change Data Capture, Temporal Tables): Ad esempio, in SQL Server è possibile attivare il SYSTEM_VERSIONED TEMPORAL TABLE e il DBMS terrà automaticamente traccia della storia delle modifiche.

Esempio (MySQL, trigger per l'audit):

CREATE TABLE user_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(10),
  old_value TEXT,
  new_value TEXT,
  changed_at DATETIME
);

DELIMITER //
CREATE TRIGGER audit_user_update
  AFTER UPDATE ON users
  FOR EACH ROW BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, changed_at)
    VALUES (OLD.id, 'UPDATE', OLD.name, NEW.name, NOW());
END//
DELIMITER ;

Domanda trabocchetto

Domanda: È sempre sufficiente avere solo il trigger di audit su UPDATE, se è necessario un protocollo completo della storia delle modifiche?

Risposta: No. Una storia completa può essere ottenuta solo combinando i trigger su tutti i tipi di operazioni: INSERT (loggare nuove righe), UPDATE (loggare modifiche) e DELETE (loggare righe eliminate).

Esempio:

-- È necessario creare 3 trigger separati per ogni operazione, altrimenti ci saranno "buchi" nella storia.

Esempi di errori reali dovuti alla mancata conoscenza delle sottigliezze dell'argomento

---

Storia 1: Un'azienda ha utilizzato solo il trigger di UPDATE per l'audit e per un anno non si è accorta che le righe eliminate non venivano registrate. Alla fine, durante l'analisi di un incidente, non sono stati in grado di determinare quando è stata eliminata un'importante registrazione dell'utente.

---

Storia 2: Un progetto ha memorizzato i vecchi e i nuovi valori dei cambiamenti in un unico campo di testo come JSON serializzato, complicando notevolmente l'analisi — sono stati necessari parser complessi e una replica separata del DB per la reportistica.

---

Storia 3: In un DB ad alto carico, gli sviluppatori hanno implementato l'audit tramite trigger, senza considerare l'aumento del volume della tabella di log. Col tempo, la tabella di storia ha superato la tabella principale di 50 volte, causando overflow dello storage e malfunzionamenti in tutto il progetto.

---