Plan du site  
pixel
pixel

Articles - Étudiants SUPINFO

Les Triggers et Curseurs dans MySQL

Par Jérémy BEDDES Publié le 31/07/2015 à 14:42:06 Noter cet article:
(0 votes)
Avis favorable du comité de lecture

Les Triggers

Tout d’abord nous allons commencer par définir ce qu'est un Trigger dans MySQL. Un Trigger est un mécanisme permettant d’indiquer à la base de données qu'elle doit exécuter une action avant ou après avoir reçu un évènement (une commande, ou bien d’autres choses). Par exemple, il est possible d’indiquer à MySQL d’exécuter une fonction avant de réaliser un INSERT ou un UPDATE dans une table.

Il faut aussi savoir qu'un Trigger exécute un traitement pour chaque ligne insérée, supprimée ou modifiée. Ainsi si l’on traite dix lignes à la fois, le traitement sera effectué dix fois. De plus, le traitement ne peut être exécuté sur la même table.

Voici la syntaxe de création d’un Trigger :

DELIMITER $$

CREATE TRIGGER trigger_utilisateur
BEFORE INSERT ON utilisateur
FOR EACH ROW
BEGIN
    [Actions à réaliser]
END $$

DELIMITER ;

Il serait par exemple possible de faire insérer des données de cette manière en mettant un INSERT dans les « Actions à réaliser ».

Nous allons maintenant prendre un exemple concret. Si vous souhaitez savoir quand de nouveaux utilisateurs ont été insérés dans la table utilisateur il vous faudra créer un Trigger qui insérera une date dans une table différente. Ainsi, il faut commencer par créer une seconde table qui servira à lister les dates d’insertion des utilisateurs.

CREATE TABLE date_utilisateur(
        id              Int(20) Auto_Increment PRIMARY KEY,
        date_insertion  DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Ensuite, reprenons le modèle de Trigger précédent et adaptons le :

DELIMITER $$

CREATE TRIGGER trigger_utilisateur
BEFORE INSERT ON utilisateur
FOR EACH ROW
BEGIN
    INSERT INTO date_utilisateur VALUES("", NOW());
END $$

DELIMITER ;

Nous avons maintenant un Trigger qui va insérer une ligne dans la table date_utilisateur pour chaque ligne insérée dans la table utilisateur.

Voici le résultat sous MySQL :

Les Triggers sont très intéressants dans le contexte de validation ou de Workflow d’une application.

Cependant il est important de garder à l’esprit que ces Triggers sont consommateurs en ressources : ils capturent des évènements et traitent des actions supplémentaires. Ces différentes étapes ralentissent tout autant le fonctionnement de la base de données. Lorsque vous pouvez vous passer des Triggers, passez-vous en. Une action peut-être aisément déportée sur la partie métier, et non sur la partie base de données dans de nombreux cas.

Nous allons maintenant aborder les Curseurs.

Les Curseurs

Un Curseur est un moyen de parcourir les résultats d’une requête SELECT et d’en traiter les valeurs une par une quel que soit le nombre de lignes qui ont été récupérées dans la requête SELECT.

Voici la syntaxe de déclaration d’un curseur :

DECLARE cursor_utilisateur CURSOR
FOR SELECT * FROM utilisateur;

Il faut savoir que la déclaration d’un curseur ne peut se faire qu'à l’intérieur d’un bloc BEGIN/END, par exemple dans une procédure stockée. Nous allons donc étudier une procédure stockée utilisant un curseur et détailler son fonctionnement.

DELIMITER $$
CREATE PROCEDURE two_users()
BEGIN

    DECLARE c_username VARCHAR(100);

    DECLARE cursor_users CURSOR
    FOR SELECT username
    FROM utilisateur

    OPEN cursor_users;

    FETCH cursor_users INTO c_username;
    SELECT c_username AS "Premier username";

    FETCH cursor_users INTO c_username;
    SELECT c_username AS "Second username";

    CLOSE cursor_users;
END $$
DELIMITER ;

Tout d’abord, il faut modifier le délimiteur pour que MySQL interprète correctement la suite. Ensuite, dans la procédure, on définit une variable c_username qui servira à prendre les valeurs données par le curseur dans la suite de la procédure. Puis il faut créer le curseur avec la syntaxe vue précédemment. C’est ici que les choses se compliquent. En effet, l’instruction « OPEN cursor_users » permet d’ouvrir le curseur pour l’utiliser. Par la suite, l’instruction « FETCH cursor_users INTO c_username » permet de donner la valeur retournée par le curseur dans la variable c_username. Il faut aussi noter que « FETCH » permet d’avancer dans les résultats du curseur. Ainsi, on ne tombera pas deux fois sur les mêmes données. Afin de terminer correctement la procédure, il ne faut pas oublier de fermer le curseur.

Voici le retour de cette procédure stockée sous MySQL :

Enfin pour terminer cette partie sur les curseurs, il faut savoir qu'ils sont utilisés dans de nombreux langages de programmation de la même façon pour parcourir les résultats d’un SELECT.

Par exemple dans une librairie en Python on peut retrouver « fetch_row » qui permet ni plus ni moins que de passer d’un élément d’un curseur à l’élément suivant :

def getResult(self, request) :
    """
    Méthode permettant de récupérer le résultat d'une requête (SELECT)
    """
    try :
        liste_resultats = []
        database = _mysql.connect(self.host, self.user, self.pwd, self.db)
        database.query(request)
        reponse = database.store_result()
        informations = reponse.fetch_row()
        # Boucle pour récupérer les informations
        for i in range(len(informations)) :
            liste_resultats.append([])
            for j in range(len(informations[i])) :
                liste_resultats[i].append(informations[i][j])
            informations = reponse.fetch_row()
        database.close()
            
    except :
        liste_resultats = []
        
    finally :
        return liste_resultats

Quels intérêts aux curseurs ? Ils offrent la possibilité de traiter les enregistrements un par un et non dans leur totalité. Cet avantage a bien évidemment un revers important : les performances sont toutes autant bridées. Chaque enregistrement nécessite son lot de traitement, contrairement à une requête classique où la majorité des traitements n’interviennent qu'une fois à la fin.

Beaucoup de codes traitent les actions après récupération complète, ce qui multiplie les requêtes ou déporte le travail sur les serveurs métiers.

A propos de SUPINFO | Contacts & adresses | Enseigner à SUPINFO | Presse | Conditions d'utilisation & Copyright | Respect de la vie privée | Investir
Logo de la société Cisco, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société IBM, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sun-Oracle, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Apple, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Sybase, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Novell, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Intel, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Accenture, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société SAP, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Prometric, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo de la société Toeic, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management Logo du IT Academy Program par Microsoft, partenaire pédagogique de SUPINFO, la Grande École de l'informatique, du numérique et du management

SUPINFO International University
Ecole d'Informatique - IT School
École Supérieure d'Informatique de Paris, leader en France
La Grande Ecole de l'informatique, du numérique et du management
Fondée en 1965, reconnue par l'État. Titre Bac+5 certifié au niveau I.
SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels