Réflexe - le blog

• meilleures performances qu’une routine exécutée dans un langage de script (exécuté dans le même espace mémoire, pas de transfert de données du serveur mysql vers le langage de script)
• possibilité d’implémenter des règles de gestion complexes à l’intérieur même du système de stockage, permet d’assurer une cohérence des données qui ne sera pas “contournable”
• possibilité de mettre à jour des champs à partir de données calculées (par exemple pour éviter un select count(*) dans une sous requête potentiellement assez gourmand)

Préalable
Télécharger MySQL Query Browser. Sauf erreur de ma part, la création des triggers (ou des procédures stockées) ne fonctionne bien ni avec le client MySQL en ligne de commande ni avec PHPMyAdmin. Par contre avec MySQL Query Browser, ça roule.

Assurez-vous d’avoir la bonne version de MySQL. Les procédures stockées ont été introduites à la version 5.

Vérifier les procédures stockées existantes
Sous MySQL 5.x, comme par exemple sous Oracle, il est possible d’accéder à un ensemble de meta-informations sur le système et sur les bases dans une base de données spécifique. Sous MySQL, cette base s’appelle INFORMATION_SCHEMA.

La table ROUTINES contient un ensemble d’informations sur les procédures stockées. La table TRIGGERS contient des informations sur les déclencheurs.

Il est donc possible de récupérer ces informations par “SELECT * FROM information_schema.triggers”.

Un petit exemple de trigger
Comme une procédure va comporter une suite d’instructions elle va contenir des “;” qui est le séparateur de commande par défaut dans MySQL.

En début de procédure, on entrera donc la commande suivante : separator //

delimiter //
DROP TRIGGER demande_before_insert//
CREATE TRIGGER demande_before_insert BEFORE INSERT ON demande
FOR EACH ROW
BEGIN
  DECLARE i_decalage NUMERIC;
  DECLARE c_file_demandes CURSOR FOR
  SELECT decalage_sec FROM
  file_demandes,
  themes
  WHERE file_demandes.id_theme = themes.id
  AND file_demandes.id = NEW.id_file_demandes;

  # valorise la date cible si elle est vide
  IF(NEW.date_objectif='0000-00-00 00:00:00' OR NEW.date_objectif IS NULL)THEN
    OPEN c_file_demandes;
    FETCH c_file_demandes INTO i_decalage;
    CLOSE c_file_demandes;
    SET NEW.date_objectif = DATE_ADD(NEW.timestamp, INTERVAL i_decalage SECOND);
  END IF;
END;//
delimiter ;

Nous avons des files de demande, chacune de ces files a un thème et fait l’objet de 0 à n demandes. On souhaite que lorsqu’on ajoute une demande, celle-ci obtienne une réponse dans un délai défini par le thème (champ decalage_sec de la table thèmes) de la file à laquelle elle appartient. À la création d’une nouvelle demande, si le champ date_objectif n’est pas renseigné, le trigger valorise calcule sa valeur à partir de la valeur renseignée dans le champ i_decalage de la table theme.

Conclusion
Sans discuter de l’intérêt de créer une procédure stockée dans ce cas précis (on pouvait très bien calculer la date cible dans un SELECT), l’intérêt majeur à mon sens de l’utilisation d’un trigger est à mon sens qu’il permet de réaliser un traitement de manière systématique sur les données insérées et ainsi d’assurer une vraie cohérence des données.

Mysql Stored Procedure Programming Un bon bouquin (en anglais) totalement consacré au sujet des procédure stockées. D’autant plus nécessaire que ce sujet est trés mal documenté dans la documentation mysql et qu’il ne traite que de ce sujet (déclencheurs et procédures stockées) et qu’à mon sens c’est le seul.

Si vous en connaissez un autre (éventuellement en français) laissez un commentaire pour l’indiquer.

Cet article a été publié le Mercredi 1 août 2007 à 20:54 et est classé dans mysql. Vous pouvez suivre les réponses reçues par cet article grâce au fil RSS 2.0. Vous pouvez laisser un commentaire, ou faire un trackback depuis votre site.