Jak zrealizować mechanizm audytu lub śledzenia zmian (historii zmian/logowania) w tabelach SQL? Jakie są podejścia, ich zalety i wady oraz co ważne jest do uwzględnienia przy projektowaniu takiej funkcjonalności?

Odpowiedź

Mechanizm audytu (log lub historia zmian) jest realizowany w celu śledzenia wszystkich zmian danych i zapewnienia przejrzystości. Istnieje kilka podstawowych podejść:

1. Wyzwalacze audytowe (Audit Triggers): Specjalne wyzwalacze AFTER/BEFORE INSERT/UPDATE/DELETE, które zapisują stare i/lub nowe stany wiersza w osobnej tabeli audytu. Zaletą jest przejrzystość dla aplikacji; wadą może być spowolnienie operacji DML.

2. Izolacja historii w osobnej tabeli (History Table): Stosuje się wersjonowanie rekordów — przy zmianie „stary” wiersz jest kopiowany do tabeli historii z oznaczeniem czasu. Główna tabela zawiera tylko aktualne dane.

3. Wbudowane mechanizmy (Change Data Capture, Temporal Tables): Na przykład w SQL Server można włączyć SYSTEM_VERSIONED TEMPORAL TABLE, a DBMS automatycznie będzie przechowywać historię zmian.

Przykład (MySQL, wyzwalacz audytu):

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 ;

Pytanie z haczykiem

Pytanie: Czy zawsze wystarczy mieć wyzwalacz audytowy tylko dla UPDATE, jeśli potrzebny jest pełny protokół historii zmian?

Odpowiedź: Nie. Pełną historię można uzyskać tylko kombinując wyzwalacze dla wszystkich typów operacji: INSERT (logować nowe wiersze), UPDATE (logować zmiany) i DELETE (logować usunięte rekordy).

Przykład:

-- Należy stworzyć 3 oddzielne wyzwalacze dla każdej operacji, w przeciwnym razie będą „dziury” w historii.

Przykłady rzeczywistych błędów z powodu braku znajomości niuansów tematu

---

Historia 1: Firma używała tylko wyzwalacza UPDATE do audytu i przez rok nie zauważyła, że usunięte rekordy nie są w żaden sposób rejestrowane. W rezultacie podczas analizy incydentu nie mogły ustalić, kiedy usunięto ważne konto użytkownika.

---

Historia 2: Projekt przechowywał stare i nowe wartości zmian w jednym polu tekstowym w postaci zserializowanego JSON, co znacznie utrudniło analizę — wymagało skomplikowanych parserów i oddzielnej replikacji DB do raportowania.

---

Historia 3: W bazie danych o dużym obciążeniu programiści wdrożyli audyt za pomocą wyzwalaczy, nie uwzględniając wzrostu objętości tabeli log. Z czasem tabela historii przekroczyła główną tabelę 50 razy, co doprowadziło do przepełnienia pamięci i awarii w całym projekcie.

---