Atelier MySQL - déclencheurs (triggers) et procédures stockées..

• Lorsque je supprime un utilisateur dans ma base, toutes les données connexes à cet utilisateur (petites annonces, news, etc.) sont également supprimées. C'est l'équivalent du "supprimer en cascade" de Access.
  • avantage n°1 : l'intégrité de la base a plus de chance d'être respectée.
  • avantage n°2 : quelle que soit la façon dont je supprime l'utilisateur, la suppression des données connexes s'effectue. Est-ce le cas dans vos bases de données actuelles ? Par exemple, quand vous supprimez un utilisateur directement dans la table à partir de PHPMyAdmin, est-ce que les enregistrements associés sont également supprimés ?
  • avantage n°3 : le code de mon application est simplifié. En effet, s'il y a plusieurs endroits où je peux supprimer un utilisateur, je n'ai pas à dupliquer le code qui supprime les données associées.
    
    
• Lorsque je réimporte la fiche d'un film dans mon site de sniffage d'Allociné, l'ancienne fiche est automatiquement copiée dans la table d'archives. Les archives de plus d'un an concernant ce film sont détruites.
  • avantage n°1 : moins de communication entre le serveur de traitements (PHP) et le serveur de base de données (MySQL).
  • avatage n°2 : simplification du code PHP.
    
    
• J'importe de temps en temps mes opérations bancaires depuis la page Web jusqu'à une base de données MySQL. L'insertion d'une opération est annulée si elle est déjà présente dans la base.
  • avantage : moins d'allers-retours entre le serveurs de traitements et le serveur de bases de données.
    
    
• Lorsqu'on ajoute une "ligne de facture", le montant de la facture est automatiquement mis à jour.
  • avantage : faires des petits traitements au moment le plus approprié, au lieu de faire un gros cumul.
    
    
• Avant une commande insert : vérifier les valeurs qui vont être insérées dans la table.
  • avantage : garde l'intégrité de la base de données sans se décharger de ce rôle sur le serveur de traitements.
    

• La syntaxe pour la création d'un déclencheur est la suivante :
  
  

  CREATE TRIGGER	trigger_name	trigger_time	trigger_event	ON tbl_name	FOR EACH ROW	trigger_statement
  Valeurs possibles :		BEFORE AFTER	INSERT UPDATE DELETE			Commande ou BEGIN   Commandes END

Note : actuellement, les déclencheurs ont les mêmes limitations que les procédures stockées : ils ne peuvent pas contenir de références directes aux tables via leur nom. Cette limitation sera levée dès que possible.

Cependant, dans la commande d'activation d'un déclencheur, vous pouvez faire référence aux colonnes dan la table associée au déclencheur en utilisant les mots OLD et NEW. OLD.col_name faire référence à une colonne d'une ligne existante avant sa modification ou son effacement. NEW.col_name faire référence à une colonne d'une ligne après insertion ou modification.

L'utilisation de SET NEW.col_name = value requiert le droit de UPDATE sur la colonne. L'utilisation de SET value = NEW.col_name requiert le droit de SELECT sur la colonne.

La commande CREATE TRIGGER requiert le droit de SUPER. Elle a été ajoutée en MySQL 5.0.2.

• Dans le cas où on a une table account, avec un champ amount, la variable @sum est mis à jour pour refléter la somme de toutes les valeurs de la colonne amount :
  
  CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount ;
  
  
• Pour tester le code ci-dessus, tapez dans MySQL:
  SET @sum = 0;
  INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
  SELECT @sum AS 'Total amount inserted';
  +-----------------------+
  | Total amount inserted |
  +-----------------------+
  | 1852.48 |
  +-----------------------+
  

• La syntaxe pour détruire un déclencheur est (doc) :
  
  DROP TRIGGER tbl_name.trigger_name

• Les déclencheurs ne peuvent pas utiliser la commande CALL, qui appelle une procédure stockée. Un déclencheur ne peut donc pas appeler une procédure stockée, il faut retaper tout le code de la procédure dans le déclencheur (lu sur la doc MySQL mais à vérifier : avec la version 5.1.36, la création n'a pas posé de problème).
  
  
• On ne peut pas avoir plusieurs déclencheurs pour le même événement sur la même table. Si on veut faire plusieurs traitements différents à un même moment, il faut les regrouper dans le même déclencheur :
    CREATE TRIGGER nom_trigger_1 BEFORE UPDATE ON même_table FOR EACH ROW set action1;
    Votre requête SQL a été exécutée avec succès ( Traitement en 0.2820 sec. )
    CREATE TRIGGER nom_trigger_2 BEFORE UPDATE ON même_table FOR EACH ROW set action2;
    #1235 - This version of MySQL doesn't yet support 'multiple triggers
    with the same action time and event for one table'
  
  
• Dans une suite de commande, on utilise le terminateur ";" (on va le voir ensuite, avec les procédures stockées). Mais ce même ";" sert également à terminer la création du trigger, d'où la nécessiter de changer ce terminateur. Par exemple, si on crée le déclencheur suivant (pour borner les valeurs insérées entre 0 et 100) :
  
  CREATE TRIGGER upd_check BEFORE UPDATE ON account
  FOR EACH ROW
  BEGIN
    IF NEW.amount < 0 THEN
      SET NEW.amount = 0; Ce ';' est interprété comme fin du CREATE TRIGGER, la suite est ignorée et cela provoque une erreur.
    ELSEIF NEW.amount > 100 THEN
      SET NEW.amount = 100;
    END IF;
  END;
  
  On va donc remplacer par :
  
  delimiter // le terminateur de commande SQL devient //.
  CREATE TRIGGER upd_check BEFORE UPDATE ON account
  FOR EACH ROW
  BEGIN
    IF NEW.amount < 0 THEN
      SET NEW.amount = 0; ';' n'est plus le terminateur de commande SQL, pas de problème.
    ELSEIF NEW.amount > 100 THEN
      SET NEW.amount = 100;
    END IF;
  END // le CREATE TRIGGER se termine grâce au nouveau terminateur.
  delimiter ; on restaure le terminateur de commande SQL.
  
  
• Si on est sous PHPMyAdmin, le terminateur de commande SQL se modifie ici :
  
  
  

Les procédures stockées sont des traitements pris en charge (développement et exécution) au niveau du serveur de données (par exemple MySQL), alors qu'ils sont traditionnellement au niveau du serveur de traitements (par exemple PHP).

• Amélioration des performances car on échange moins de données entre le serveur et le client.
  Par exemple : je veux lancer sur mes données une "recherche approximative" (les champs ressemblent au mot-clé de la recherche) :
  - soit je lance une requête qui rapatrie toutes les données vers le serveur d'application, puis ce serveur d'application lance la fonction recherche_approximative() sur tous les enregistrements.
  - soit je crée la fonction stockée recherche_approximative() : c'est le serveur de données qui va exécuter la fonction dans une requête, la requête ne va renvoyer au serveur de données que les résultats de la recherche.
  
  
• Déporter des traitements du serveur de traitements vers le serveurs de données, dans le cas où le serveur de données serait moins chargé que le serveur de traitements (plutôt inutile dans le cas où les deux serveurs utilisent le même processeur/coeur).
  
  
• Centraliser un traitement dans le cas où la base de données est appelée à être utilisée par plusieurs applications clientes différentes. Au lieu de redévelopper la même fonctionnalité dans chaque application, on la développe au niveau du serveur de données.
  Par exemple : tests de validité des données insérées.
  

• Voir la doc officielle sur : create, alter, drop, call, begin...end, declare, les variables, conditions, curseurs, instructions de contrôle.

• Cherchez, sur internet, les information sur la fonction de distance de chaîne de caractères : Levensthein.
  Remarque : la fonction Levensthein est implémentée nativement en PHP, mais pas sous MySQL.
  
  
• Cherchez la fonction stockée toute faite qui implémente cette fonction dans votre serveur MySQL.
  
  
• Testez cette fonction stockée sur une de vos bases de données, en faisant une requête approximative.
  
  Requête de recherche normale :
  SELECT * FROM client WHERE nom = 'mot-clé' ;
  
  Requête de recherche approximative :
  SELECT * FROM client WHERE Levensthein(nom, 'mot-clé') < 10 ;

• Dans un trigger, on veut faire référence aux valeurs des champs en cours d'insertion ou de modification NEW.nom_champ. Par exemple :
  
  CREATE TRIGGER nom_trigger
  BEFORE UPDATE ON nom_table
  FOR EACH ROW INSERT INTO table SELECT * FROM table2 WHERE champ = NEW.nom_champ ;
  
  
• Exemple de trigger créé pour donner la valeur par défaut timestamp courant à un champ (pourquoi ? parce que par défaut, on ne appliquer ça qu'à un seul champ d'une table. Quand on veut mettre le timestamp courant à deux champs, on est coincé). Problème soulevé par Julien Courtes.
  
  CREATE TRIGGER set_time_stop BEFORE INSERT ON nom_table
  FOR EACH ROW SET NEW.time_stop = CURRENT_TIMESTAMP()
  

Ceci a été testé en MySQL 5.1.30 et risque de ne pas marcher en 5.0 (interdit de faire un SELECT dans un trigger, interdit d'appeler une fonction à partir d'un trigger).

Cette démonstration utilise une table user avec un champ login, où la valeur NULL est interdite :

CREATE TABLE IF NOT EXISTS `user` (`login` varchar(255) NOT NULL);

___________________________________________________
Une simple fonction qui retourne une constante, elle reçoit un paramètre inutile

DROP FUNCTION IF EXISTS test;
CREATE FUNCTION test (i VARCHAR(255)) RETURNS VARCHAR(255) RETURN 'coucou';
SELECT test('abcd');
___________________________________________________
La même fonction mais dans un bloc BEGIN...END, nécessite DELIMITER

DROP FUNCTION IF EXISTS test;

DELIMITER //
CREATE FUNCTION test (i VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
RETURN 'coucou';
END//
DELIMITER ;

SELECT test('abcd');
_______________________________________________________
La même fonction renvoie le nombre de logins dans la table user

DELIMITER //

DROP FUNCTION IF EXISTS test //

CREATE FUNCTION test (i VARCHAR(255)) RETURNS INT
BEGIN
    SELECT count(*) FROM user INTO @result;
    RETURN @result;
END//
DELIMITER ;

SELECT test('abcd');
_______________________________________________________
La fonction login_existe renvoie 0 ou 1 si le login reçu en paramètre est déjà dans la table

DELIMITER //

DROP FUNCTION IF EXISTS login_existe //

CREATE FUNCTION login_existe (newlogin VARCHAR(255)) RETURNS INT
BEGIN
    SELECT count(*) FROM user WHERE login = newlogin INTO @result;
    RETURN @result;
END//
DELIMITER ;

SELECT login_existe('spastore');
_______________________________________________________
Le trigger qui annule l'insertion si le login est déjà dans la table user :

DELIMITER //

DROP TRIGGER IF EXISTS valide_insertion //

CREATE TRIGGER valide_insertion BEFORE INSERT ON user FOR EACH ROW
BEGIN
    IF login_existe(NEW.login) THEN
        SET NEW.login=NULL;
    END IF;
END //

DELIMITER ;

Remarque : qu'est-ce que c'est ce SET NEW.login=NULL qui fait salement planter la requête ? Sous MySQL, à l'heure actuelle (5.1.30), c'est la seule solution pour que le trigger annule l'insertion dans la table. Contrairement aux autres SGBD, il n'esiste pas de solution propre genre "ROLLBACK". Ceci est en préparation, un RETURN FALSE; devrait bientôt résoudre le problème.
_______________________________________________________
La même chose, en un seul trigger (sans passer par la fonction login_existe)

DELIMITER //

DROP TRIGGER IF EXISTS valide_insertion//

CREATE TRIGGER valide_insertion BEFORE INSERT ON user FOR EACH ROW
BEGIN
    SELECT count(*) FROM user WHERE login = NEW.login INTO @result;
    IF @result THEN
        SET NEW.login=NULL;
    END IF;
END //

DELIMITER ;