Mise en place d’un historique des enregistrements sous MySQL


  • Pourquoi faire ?

Dans une base de données, il peut être très utile de disposer d’un historique des modifications successives des enregistrements. Cela permet par exemple de gérer des versions de vos données avec la possibilité de revenir à une version précédente ou bien de pouvoir visualiser les différences entre deux versions. Cela peut également être utile dans le cas où, pour des raisons de sécurité par exemple, vous souhaitez être capable de savoir quel utilisateur a effectué quelles modifications sur les données.

  • Mécanisme utilisé

Nous allons donc voir comment mettre en place ce système avec MySQL 5.x et supérieur. La technique utilisée est celle des « triggers ». Sous ce terme un peu barbare se cache simplement la possibilité de déclencher une ou plusieurs requêtes SQL lorsqu’un enregistrement est ajouté, modifié ou bien supprimé dans une table SQL donnée. Grâce à ce mécanisme, vous pourriez par exemple compter le nombre de fois où une table est modifiée. Nous allons utiliser ce système de triggers pour mettre en place notre historique des enregistrements.

  • La table « maître »

Pour illustrer concrètement notre exemple, nous allons considérer une base de données avec la table suivante :

CREATE TABLE utilisateur (
  id BIGINT NOT NULL AUTO_INCREMENT,
  version BIGINT NOT NULL DEFAULT 0,
  nom VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY (version)
);

Comme vous le voyez, la structure de cette table est extrèmement simple. Cela n’a pas d’importance, la méthode que nous allons voir maintenant peut s’appliquer à n’importe quelle table quelle que soit sa complexité. Remarquez la présence d’un champ « version » qui contiendra le numéro de version de l’enregistrement.

  • La table d’historique

Pour sauvegarder l’historique des enregistrements, nous allons à chaque modification ou suppression d’un enregistrement dans la table « utilisateur » recopier les anciennes données dans une autre table que nous appellerons « histo_utilisateur ». Cette table DOIT impérativement inclure tous les champs de la table « utilisateur » puisque nous allons recopier l’intégralité de l’enregistrement vers cette table d’historique. Elle comportera également quelques champs supplémentaires que nous détaillerons par la suite. Voici donc la structure de notre table d’historique :

CREATE TABLE histo_utilisateur (
  id BIGINT NOT NULL AUTO_INCREMENT,
  action ENUM ( 'update' , 'delete' ) DEFAULT NULL,
  date_action DATETIME DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 0,
  id_original BIGINT NOT NULL DEFAULT 0,
  nom VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY (id_original),
  KEY (action),
  KEY (date_action),
  KEY (version)
);

Nous retrouvons bien dans cette table le champ « nom » de la table d’origine. Le champ « id_original » va nous permettre de sauvegarder le champ « id » de la table d’origine. Ce qui nous permet de conserver un champ « id » pour l’identifiant unique de la table d’historique.

Le champ « action » permet de conserver le type d’événement qui a conduit à la recopie des données. Il permet également de savoir si l’enregistrement original existe toujours. En effet, si cet enregistrement a été supprimé, le champ action aura la valeur « delete ». Le champ « date_action » permet de connaître la date et l’heure de la copie.

Le champ « version » sert à stocker le numéro de version d’enregistrement. Il s’agit ici tout simplement d’un nombre qui sera incrémenté à chaque recopie de données.

  • Définition des triggers

Nous pouvons maintenant mettre en place les triggers nécessaires pour la gestion de l’historique. De quoi avons-nous besoin ? :

– A chaque modification d’un enregistrement de la table « utilisateur », il nous faut recopier les données dans la table « histo_utilisateur » et gérer les champs qui servent à l’historique. C’est à dire incrémenter le numéro de version, renseigner le champ « action » à la valeur « update » et le champ « date_action » à la date et heure courante.

– A chaque suppression d’un enregistrement de la table « utilisateur », il nous faut recopier les données (qui vont être supprimées dans la table d’origine) et là encore gérer les champs qui servent à l’historique. C’est à dire incrémenter le numéro de version, renseigner le champ « action » cette fois-ci avec la valeur « delete » et le champ « date_action » à la date et heure courante.

Cela se traduit en SQL avec les instructions suivantes :

delimiter //
CREATE TRIGGER trig_avant_update_utilisateur
BEFORE UPDATE ON utilisateur FOR EACH ROW
  BEGIN
    SET NEW.version = OLD.version + 1;
    INSERT INTO histo_utilisateur
      (action, date_action, version, nom, id_original)
    VALUES
      ('update', NOW(), OLD.version, OLD.nom, OLD.id);
  END;
//
CREATE TRIGGER trig_apres_delete_utilisateur
AFTER DELETE ON utilisateur FOR EACH ROW
  BEGIN
    INSERT INTO histo_utilisateur
      (action, date_action, version, nom, id_original)
    VALUES
      ('delete', NOW(), OLD.version, OLD.nom, OLD.id);
  END;
//
delimiter ;

L’instruction « delimiter » indique à MySQL de modifier le délimiteur de commande (par défaut le caractère « ; ») avec la séquence de caractères « // ». Cela nous permet d’utiliser le caractère « ; » dans la définition des triggers sans qu’il soit interprété par le client MySQL.

Le premier trigger appelé « trig_avant_update_utilisateur » indique qu’avant chaque mise à jour d’un enregistrement de la table « utilisateur » les actions suivantes seront effectuées :

– Incrément du numéro de version pour l’enregistrement de la table « utilisateur ». Les pseudo noms d’enregistrements « NEW » et « OLD » indiquant respectivement l’ancien et le nouvel enregistrement de la table concernée par le trigger (donc ici l’enregistrement mis à jour de la table « utilisateur »).

– Insertion d’un enregistrement dans la table « histo_utilisateur » avec recopie des données (via l’utilisation du pseudo nom d’enregistrement « OLD ») et renseignement des champs dédiés à la gestion de l’historique.

Le deuxième trigger appelé « trig_apres_delete_utilisateur » indique qu’après chaque suppression d’un enregistrement de la table « utilisateur » les actions suivantes seront effectuées :

– Insertion d’un enregistrement dans la table « histo_utilisateur » avec recopie des données (via l’utilisation du pseudo nom d’enregistrement « OLD ») et renseignement des champs dédiés à la gestion de l’historique.

  • Passons aux tests !

Nous allons maintenant vérifier que tout fonctionne. Connectez-vous à votre serveur MySQL et créez une base de données de test :

mysql> CREATE DATABASE test_cache;

Vous devez évidemment avoir les droits suffisants pour effectuer cette opération.

Créez ensuite les tables et les triggers en utilisant les instructions SQL que nous avons vu précedemment. Nous allons maintenant créer un premier enregistrement dans la table « utilisateur » :

mysql> INSERT INTO utilisateur (nom) VALUES ("Homer Simpson");

Regardons ensuite le contenu de nos deux tables :

mysql> SELECT * FROM utilisateur;
+----+---------+----------------+
| id | version | nom            |
+----+---------+----------------+
|  1 |       0 | Homer Simpson  |
+----+---------+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM histo_utilisateur;
Empty set (0.00 sec)

Nous avons bien notre enregistrement dans la table « utilisateur » et aucun enregistrement dans la table d’historique. Ce qui est normal puisque nous n’avons créé aucun trigger dans le cas d’une insertion d’un enregistrement dans la table « utilisateur ».

Modifions maintenant notre enregistrement :

mysql> UPDATE utilisateur SET nom="Donuts" WHERE id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Regardons maintenant à nouveau le contenu de nos deux tables :

mysql> SELECT * FROM utilisateur;
+----+---------+--------+
| id | version | nom    |
+----+---------+--------+
|  1 |       1 | Donuts |
+----+---------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM histo_utilisateur;
+----+--------+---------------------+---------+-------------+----------------+
| id | action | date_action         | version | id_original | nom            |
+----+--------+---------------------+---------+-------------+----------------+
|  1 | update | 2008-08-17 14:13:03 |       0 |           1 | Homer Simpson  |
+----+--------+---------------------+---------+-------------+----------------+
1 row in set (0.00 sec)

Notre trigger a bien fonctionné ! L’enregistrement d’origine a bien été modifié. Et la table d’historique contient bien une copie de l’enregistrement précédent. Par ailleurs, le numéro de version de l’enregistrement présent dans la table « utilisateur » a bien été incrémenté.

Supprimons maintenant notre enregistrement :

mysql> DELETE FROM utilisateur WHERE id=1;
Query OK, 1 row affected (0.02 sec)

Puis regardons à nouveau le contenu de nos deux tables :

mysql> SELECT * FROM utilisateur;
Empty set (0.00 sec)

mysql> SELECT * FROM histo_utilisateur;
+----+--------+---------------------+---------+-------------+----------------+
| id | action | date_action         | version | id_original | nom            |
+----+--------+---------------------+---------+-------------+----------------+
|  1 | update | 2008-08-17 14:13:03 |       0 |           1 | Homer Simpson  |
|  2 | delete | 2008-08-17 14:16:59 |       1 |           1 | Donuts         |
+----+--------+---------------------+---------+-------------+----------------+
2 rows in set (0.00 sec)

Là encore, le trigger a bien été pris en compte. L’enregistrement d’origine a été supprimé. La table d’historique contient bien une copie de l’enregistrement supprimé.

  • Conclusion

Nous avons vu un procédé très simple à mettre en place afin de conserver l’historique des modifications et suppressions dans une base de données. L’exemple s’appuie sur MySQL mais est évidemment transposable sur toutes les autres bases de données qui gèrent les triggers. Le choix de gérer l’historique dans une deuxième table dédiée et non pas dans la table d’origine permet de clairement séparer les données actives des données d’historique. Par ailleurs, cela permet également d’éliminer tout impact sur les performances des requêtes effectuées sur les données actives. Il reste ensuite à ajouter une gestion de la durée de conservation des données d’historique afin d’éviter une explosion de la taille des tables dédiées aux données d’historique.

  • Références

Section « Triggers » de la documentation MySQL :

http://dev.mysql.com/doc/refman/5.0/fr/triggers.html

Publicités

15 réflexions sur “Mise en place d’un historique des enregistrements sous MySQL

  1. Salut,merci pour le ce tuto (après 3 ans ) , je veux savoir comment mettre en place une historique pour toutes les tables d’une BD avec le nom d’user qui a effectué la requête et merci d’avance

  2. Je suis un hyper hyper débutant dans les triggers et J’adore !!!
    Merci beaucoup pour ce tuto génial et très très explicite. (^_^)
    Je crois que je vais faire un pas en plus vers la gestion de l’historique de toutes mes tables 🙂

    Mais à quoi ressemblerait la syntaxe d’un trigger d’insert dans ce cas ?

    • Bonjour,

      Pour un trigger sur les requêtes INSERT, ce sera par exemple :

      CREATE TRIGGER trig_avant_insert_utilisateur
        BEFORE INSERT ON utilisateur FOR EACH ROW
        ...
      

      En résumé, par rapport aux exemples précédents, il suffit de remplacer le « BEFORE UPDATE » par « BEFORE INSERT » pour déclencher le trigger avant l’exécution de la requête INSERT. Je vous invite à consulter la documentation MySQL pour la syntaxe de CREATE TRIGGER : http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

    • Cela me paraît difficile de le gérer avec un système de triggers. Vous pouvez par contre le gérer au niveau de votre application. Par exemple, après chaque modification, ajouter un enregistrement dans une table dédiée à l’historique des actions effectuées dont un des champs indique l’identifiant de l’utilisateur qui a effectué l’action en question.

      • En effet Stéphane, c’est la meilleure procédure pour conserver cette information qui est présente dans une variable session de l’application.

    • Si si, il s’incrémente automatiquement. C’est la ligne « SET NEW.version = OLD.version + 1; » du trigger « trig_avant_update_utilisateur ».

  3. Je ne vois d’intérêt au numéro de version que si l’on veut pouvoir revenir à un état antérieur, ce qui n’est souvent pas nécessaire

  4. merci pour ce tuto mais il ya un probléme , quand j’ai essayé d’integrer ce code de trigger dans ma BD , il ya une erreur qui s’affiche j’ai pas compris pkoi :
    l’erreur c’est :

    Erreur
    Il semble qu’il y ait une erreur dans votre requête SQL. Le message ci-bas peut vous aider à en trouver la cause.

    ERROR: Ponctuation invalide @ 11
    STR: //
    SQL: delimiter //
    CREATE TRIGGER trig_avant_update_utilisateur
    BEFORE UPDATE ON utilisateur FOR EACH ROW
    BEGIN
    SET NEW.version = OLD.version + 1;

    requête SQL:

    delimiter // CREATE TRIGGER trig_avant_update_utilisateur BEFORE UPDATE ON utilisateur FOR EACH ROW BEGIN SET NEW.version = OLD.version + 1;

    MySQL a répondu:Documentation

    #1064 – Erreur de syntaxe près de ‘delimiter //
    CREATE TRIGGER trig_avant_update_utilisateur
    BEFO’ à la ligne 1

    • J’ai effectué un test sous MySQL 5.6.22 en effectuant de simples copier-coller des requêtes. Aucun problème. Vérifiez qu’il ne vous manque pas un caractère espace quelque part par exemple. De mon côté, les requêtes indiquées dans l’article fonctionnent bien.

  5. Merci pour ce tuto avec quelques années de retard.
    Dans le cadre du développement d’un logiciel spécifique pour notre système de management qualité, je dois gérer les historiques de modifications des informations et je me demandais comment le faire
    ++

    • La méthode décrite dans l’article peut vous aider mais, sans davantage de détails, cela m’est difficile de vous répondre plus précisément.

  6. Bonjour,
    Je n’ai pas de questions en particulier et je n’attendais donc pas de réponse. Votre méthode m’a juste éclairé sur une piste de démarrage pour la gestion des historiques de modifications des tables.
    Merci encore
    ++

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s