Hoe implementeer je een auditmechanisme of wijzigingstracking (wijzigingsgeschiedenis/logging) in SQL-tabellen? Welke benaderingen zijn er, wat zijn de voor- en nadelen, en wat is belangrijk om in gedachten te houden bij het ontwerpen van deze functionaliteit?

Antwoord

Het auditmechanisme (log of wijzigingsgeschiedenis) wordt geïmplementeerd om alle gegevenswijzigingen te volgen en transparantie te waarborgen. Er zijn verschillende belangrijke benaderingen:

1. Audit Triggers: Speciale AFTER/BEFORE INSERT/UPDATE/DELETE-triggers die de oude en/of nieuwe toestand van een rij in een aparte audit-tabel opslaan. Voordeel — transparantie voor de applicatie; nadeel — mogelijke vertraging van DML-operaties.

2. Isolatie van geschiedenis in een aparte tabel (History Table): Wijzigingen worden gecopieerd naar een history-tabel met een tijdstempel. De hoofdtafel bevat alleen actuele gegevens.

3. Ingebouwde mechanismen (Change Data Capture, Temporal Tables): Bijvoorbeeld, in SQL Server kan SYSTEM_VERSIONED TEMPORAL TABLE worden ingeschakeld, en de DBMS slaat automatisch de geschiedenis van wijzigingen op.

Voorbeeld (MySQL, trigger voor 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 ;

Vraag met een valstrik

Vraag: Is het altijd voldoende om alleen een audit-trigger voor UPDATE te hebben als een volledige wijzigingsgeschiedenis vereist is?

Antwoord: Nee. Een volledige geschiedenis kan alleen worden verkregen door triggers voor alle soorten bewerkingen te combineren: INSERT (log nieuwe rijen), UPDATE (log wijzigingen) en DELETE (log verwijderde records).

Voorbeeld:

-- Je moet 3 afzonderlijke triggers voor elke bewerking maken, anders zijn er "gaten" in de geschiedenis.

Voorbeelden van echte fouten door onbekendheid met de subtiliteiten van dit onderwerp

---

Verhaal 1: Een bedrijf gebruikte alleen een UPDATE-trigger voor audit en merkte een jaar lang niet op dat verwijderde records niet werden vastgelegd. Uiteindelijk kon men bij de analyse van een incident niet achterhalen wanneer een belangrijke gebruikersaccount was verwijderd.

---

Verhaal 2: Het project bewaarde oude en nieuwe wijzigingswaarden in één tekstveld in de vorm van geserialiseerde JSON, wat de analyse aanzienlijk bemoeilijkte - ingewikkelde parsers en een aparte database-replicatie voor rapportage waren nodig.

---

Verhaal 3: In een database met hoge belasting implementeerden ontwikkelaars audit via triggers, zonder rekening te houden met de groei van de omvang van de log-tabel. Na verloop van tijd overschreed de geschiedenis-tabel de hoofdtafel met 50 keer, wat leidde tot opslagproblemen en storingen in het hele project.

---