Exercices les Triggers en MySQL : Série 02

• CLIENT (NUMCL, NOM, PRENOM, ADR, CP, VILLE, SALAIRE, CONJOINT)
• DETENTEUR (idDet, NUMCL, NUMCP)
• COMPTE (NUMCP, DATEOUVR, SOLDE)

Attributs soulignés : Clés primaires. Attributs en italiques: Clés étrangères.
NUMCL et CONJOINT sont définis sur le même domaine.

• Écrire un trigger en insertion permettant de contrôler les contraintes suivantes :
• Le département dans lequel habite le client doit être 01, 07, 26, 38, 42, 69, 73, ou 74 ;
• Le nom du conjoint doit être le même que celui du client.
• Définir un trigger en insertion permettant d’implémenter une numérotation automatique de la clé primaire de la table DETENTEUR sachant qu’il est composé de dix chiffres. Le premier numéro doit être 1111111110.

Travail demandé

1. Rédiger le script de création de tables relatif à la base de données ci-avant schématisée.
2. Exprimer les exigences suivantes en utilisant des déclencheurs :
1. Chaque Cours a un effectif maximum de 12 élèves.
2. Seuls les professeurs du spécialité « Mathématiques » peuvent enseigner plus qu’un cours.
3. Chaque élève doit être inscrit au cours « Mathématiques ».
4. Le nombre d’élèves inscrits dans la section base de données doit être supérieur au nombre d’élèves dans la section Mathématiques.
5. Les professeurs de différents départements ne peuvent pas enseigner dans la même salle.

1. Rédiger le script de création de tables relatif à la base de données ci-avant schématisée.
2. Écrire les triggers permettant de contrôler les contraintes suivantes :
1. Lorsqu’on insère un nouveau client ou modifie un client existant, son nom doit être écrit en majuscules. S’il n’est pas connu, le segment doit avoir la valeur « Inconnu ».
• Pour tester :
• Insérez un nouveau client avec un nom en minuscules et un segment null. Vérifiez que le nom a été inséré en majuscules et que le segment a pour valeur « Inconnu »
• Modifiez le nom d’un client existant (toujours en minuscules). Vérifiez que le nom a été mis en majuscules
• Que se passe-t-il si vous modifiez un autre attribut que le nom d’un client existant (par exemple son segment)?
2. Lorsqu’on insère ou modifie un nouvel avis, la date de l’avis doit toujours être inférieure ou égale à la date du jour. Autrement dit :
• si la date d’un nouvel avis est supérieure à la date du jour, on insérera cet avis avec la date du jour ;
• Pour tester : insérez un nouvel avis avec une date supérieure à la date du jour. Vérifiez que l’avis a été inséré avec la date du jour
• on ne doit pas pouvoir modifier la date d’un avis existant avec une date supérieure à la date du jour.
• Dans une première version de solution, vous laisserez simplement l’ancienne date.
• Dans une deuxième version, vous afficherez un message d’erreur plus explicite en créant une table Erreur et en suivant les étapes présentées dans le document « Administrez vos bases de données avec MySQL »
• Pour tester : modifiez la date d’un avis existant avec une date supérieure à la date du jour. Vérifiez que la date de l’avis n’a pas été modifiée (version 1) ou que le message d’erreur s’affiche (version 2)..
3. Complétez l’un des triggers de la question 1) pour qu’un client ne puisse pas changer de segment, sauf si le segment était « Inconnu » jusqu’à présent.
• Pour tester :
• Modifiez le segment d’un client existant dont le segment était inconnu. Vérifiez que le segment a été modifié.
• Modifiez le segment d’un client existant dont le segment était connu. Vérifiez que le segment n’a pas été modifié.
4. Si on supprime la dernière vente d’un client on supprime aussi le client.
• Pour tester :
• Insérez un nouveau client dans la table CLIENT ainsi que 3 nouvelles ventes pour ce client dans la table VENTES.
• Supprimez une des ventes de ce client. Vérifiez que le client existe toujours dans la table CLIENT.
• Supprimez une autre des ventes de ce client. Vérifiez que le client existe toujours dans la table CLIENT.
• Supprimez « la dernière vente » de ce client. Vérifiez que le client n’existe plus dans la table CLIENT.
5. L’ajout d’une nouvelle vente d’un client pour un produit entraine automatiquement l’ajout d’un avis de ce client pour ce produit, avec la note 3 (cet exemple est complètement fictif évidemment J).
• Pour tester :
• Insérez une nouvelle vente d’un produit pour un client dans la table VENTES.
• Vérifiez qu’un nouvel avis a été créé dans la table AVIS pour ce client et ce produit, avec une note de 3 et une date pertinente.
• A votre avis, les triggers programmés en question 2 se sont-ils déclenchés ? Que s’est il passé ?