Comment mettre en œuvre un mécanisme d'audit ou de suivi des modifications (historique des modifications/logs) dans des tables SQL ? Quels sont les approches, leurs avantages et inconvénients, et quoi prendre en compte lors de la conception de cette fonctionnalité ?

Réponse

Le mécanisme d'audit (log ou historique des modifications) est mis en œuvre pour suivre tous les changements de données et assurer la transparence. Il existe plusieurs approches principales :

1. Déclencheurs d'audit (Audit Triggers) : Des déclencheurs AFTER/BEFORE INSERT/UPDATE/DELETE spéciaux qui conservent l'état ancien et/ou nouveau d'une ligne dans une table d'audit distincte. Avantage — transparence pour l'application ; inconvénient — ralentissement potentiel des opérations DML.

2. Isolation de l'historique dans une table distincte (History Table) : Utilisation de la version des enregistrements — lors d'un changement, l'ancienne ligne est copiée dans une table d'historique avec un horodatage. La table principale contient uniquement des données actuelles.

3. Mécanismes intégrés (Change Data Capture, Temporal Tables) : Par exemple, dans SQL Server, il est possible d'activer SYSTEM_VERSIONED TEMPORAL TABLE, et la SGBD stockera automatiquement l'historique des modifications.

Exemple (MySQL, déclencheur pour 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 ;

Question piège

Question : Est-il toujours suffisant d'avoir un déclencheur d'audit uniquement sur UPDATE si un protocole complet de l'historique des modifications est requis ?

Réponse : Non. L'historique complet ne peut être obtenu qu'en combinant des déclencheurs sur tous les types d'opérations : INSERT (logger les nouvelles lignes), UPDATE (logger les changements) et DELETE (logger les enregistrements supprimés).

Exemple :

-- Il est nécessaire de créer 3 déclencheurs séparés pour chaque opération, sinon il y aura des "vides" dans l'historique.

Exemples d'erreurs réelles dues à une méconnaissance des subtilités du sujet

---

Histoire 1 : L'entreprise a utilisé uniquement un déclencheur UPDATE pour l'audit et n'a pas remarqué pendant un an que les enregistrements supprimés n'étaient pas enregistrés. En fin de compte, lors de l'analyse d'un incident, il a été impossible de déterminer quand un important compte utilisateur a été supprimé.

---

Histoire 2 : Le projet a stocké les anciennes et nouvelles valeurs des modifications dans un même champ texte sous forme de JSON sérialisé, ce qui a considérablement compliqué l'analyse - des parseurs complexes et une réplique séparée de la base de données étaient nécessaires pour les rapports.

---

Histoire 3 : Dans une base de données à forte charge, les développeurs ont mis en œuvre l'audit via des déclencheurs sans tenir compte de la croissance du volume de la table de log. Avec le temps, la table d'historique a dépassé la table principale de 50 fois, ce qui a entraîné un débordement de stockage et des échecs dans tout le projet.

---