sio2:exercices_d_applications [SIO Wiki]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
~~SLIDESHOW~~
====== Exemples de cas d'utilisation de triggers ======

====== 1) Utiliser des champs calculés ======
Bien que les champs calculés constituent une forme de redondance de données, il est parfois commode d’en utiliser afin d’optimiser les performances de certaines requêtes. En effet, l’usage de champs calculés peut servir aux fins par exemple d’éviter des jointures. 

=====  Exemple : =====
On imagine une base de données de gestion de commandes, afin de faciliter les calculs concernant les ventes réalisées, on souhaite : 
 
  - Gérer automatiquement les attributs totalHTLigne, totalTVALigne et totalTTCLigne à l’entité ligneCommande. Ces propriétés doivent être calculées. 
  - Gérer automatiquement les attributs montantHT, montantTVA et montantTTC à l’entité Commande. Ces propriétés doivent être calculées automatiquement. 
 
**Quelle solution possible ?** 
 
  - A l’insertion ainsi qu’à la modification d'une ligne de commande, calculer ou recalculer automatiquement les champs totalHTLigne , totalTVALigne et totalTTCLigne de la ligne de commande; 
  - A l’insertion ainsi qu’à la modification et à la suppression d'une ligne de commande, calculer ou recalculer automatiquement les champs MontantHT, MontantTVA et MontantTTC de la commande.  

[[https://drive.google.com/file/d/1w8lGRdPTS7Fz8xx2cV6XRS0fyu5imazi/view?usp=drive_link|script de création de la bd]]

**Trigger1 : Quand se déclenche t'il ? Que fait-il ? Testez-le. Contrôlez son exécution**
<code>
DROP TRIGGER IF EXISTS calculLCBI; 
DELIMITER $$
CREATE TRIGGER calculLCBI BEFORE INSERT ON ligneCommande FOR EACH ROW 
BEGIN
  	-- à compléter 
  	SELECT prixHT, tauxTVA INTO @prix, @taux 
  	FROM produit 
  	WHERE idproduit = NEW.idproduit ; 
	-- à compléter
  	SET NEW.totalHTLigne = @prix * NEW.quantite ; 
  	SET NEW.totalTVALigne= NEW.totalHTLigne* @taux/100 ; 
        SET NEW.totalTTCLigne= NEW.totalHTLigne + NEW.totalTVALigne;  
END$$ 
DELIMITER ;
</code>

<badge>A vous de jouer : Autres déclencheurs utiles ? Ecrire et tester les autres déclencheurs</badge>

====== 2) Vérifier une contrainte d'entités ======
Les mécanismes d’héritage ou de spécialisation peuvent être modélisés. Pour autant, si aucun traitement spécifique n’est fait nous ne sommes pas capables de nous assurer qu’une telle contrainte soit vérifiée. 

=====  Exemple : =====
Dans notre gestion commerciale, un produit est soit un produit alimentaire - et on veut connaître sa date limite de consommation, soit un produit électroménager - et on veut connaitre sa classe énergétique.

Si aucun traitement spécifique n’est fait :
  * Rien n’empêche pourtant qu’on ait un produit ne figurant ni parmi les produits alimentaires, ni parmi les produits électroménagers.  
  * Rien n’empêche non plus qu’on ait un produit figurant tout à la fois parmi les produits alimentaires et les produits électroménagers

==== Quelles solutions possibles ? (dépend des tables créés) ====
=== 1ère solution : 1 table = utiliser les contraintes de domaine ===

<code>
CREATE TABLE produit (
    idProduit INT AUTO_INCREMENT PRIMARY KEY,
    nomProduit VARCHAR(255) NOT NULL,
    prixHt DECIMAL(10, 2),     -- Prix hors taxe
    tauxTva DECIMAL(5, 2),     -- Taux de TVA en pourcentage
    typeProduit ENUM('alimentaire', 'electromenager') NOT NULL CHECK (
        (typeProduit = 'alimentaire' AND dateLimiteConsommation IS NOT NULL AND classeEnergetique IS NULL) OR 
        (typeProduit = 'electromenager' AND classeEnergetique IS NOT NULL AND dateLimiteConsommation IS NULL)
    ),  -- Type de produit
    dateLimiteConsommation DATE,        -- Date limite pour produits alimentaires
    classeEnergetique VARCHAR(5)     -- Classe énergétique pour électroménager
       )      ;
</code>
<badge>A vous de jouer</badge>
  - réimporter le script de création de la bd de gestion des commandes en changeant le nom de la base de données(gestionCommandeExo2) et changer la table produit pour la remplacer avec celle ci-dessus
  - tester les contraintes : quels tests faites-vous ?
  - analyser les messages d'erreurs, comment les rendre plus clairs ?
=== 2ème solution : 3 tables = gérer les relations d'héritage, avec la spécialisation ===
  * 1 Table de base (produit) : Contient les attributs communs à tous les produits, comme le nom, le prix hors taxe et le taux de TVA.
  * 2 Tables spécialisées (alimentaire, electromenager) : Contiennent les attributs spécifiques à chaque type de produit avec une clé primaire qui est une clé étrangère (idProduit) pointant vers la table produit.
  * Contrainte d'intégrité : On utilise des clés étrangères dans les tables spécialisées pour faire référence à la table principale produit, garantissant qu'un produit dans une table spécialisée existe toujours dans la table de base.
<badge>A vous de jouer</badge>
  - proposer le schéma relationnel des 3 tables 
  - réimporter le script de création de la bd de gestion des commandes en changeant le nom de la base de données(gestionCommandeExo3) et changer la table produit pour la remplacer par les 3 tables
  - tester les contraintes : quels tests faites-vous ?
  - analyser les messages d'erreurs, comment les rendre plus clairs ?

**Explication des contraintes d'intégrité :**
  * __Intégrité référentielle__ : Chaque produit alimentaire ou électroménager doit avoir une entrée correspondante dans la table produit. Cette intégrité est garantie par les clés étrangères (idProduit) dans les tables alimentaire et electromenager, qui pointent vers la table produit.
  * __Clé primaire partagée__ : Les tables spécialisées alimentaire et électromenager partagent la même clé primaire que la table produit, ce qui doit permettre de garantir qu'un produit ne peut être à la fois dans alimentaire et électromenager.
  * __Suppression en cascade__ : Lorsqu'un produit est supprimé de la table produit, l'entrée associée dans la table alimentaire ou électroménager sera également supprimée automatiquement grâce à l'option ON DELETE CASCADE.

<alert type="warning">Toutes les contraintes sont elles vérifiées ?</alert>
<badge>A vous de jouer : testez chacune des 2 solutions</badge>
<alert type="success">CONSEIL : Travailler sur des bases de données différentes pour réaliser vos tests</alert>

====== 3) Vérifier une contrainte d’associations  ======
=====  Trigger pour vérifier une contrainte d'inclusion =====
On ajoute à notre gestion commerciale la gestion des livraisons des commandes : un produit ne doit pouvoir être livré que si il a été commandé et sa quantité livrée ne doit pas dépasser la quantité commandée bien sûr. On simplifie le cas en définissant qu'une livraison ne concerne qu'une seule commande à la fois et un produit est livré en une seule fois.

===== Quels problèmes ? =====
  * Rien ne nous assure que les livraisons portent sur les produits commandés correspondant ; 
  * Rien ne nous assure que les quantités livrées ne soient pas supérieures à celles commandées(si la quantité est inférieure il y aura un avoir non géré ici donc la quantité livrée inférieure ne pose pas de problème ici). 
 
===== Quelle solution possible ? =====
 
  - A l’insertion comme à la modification d’une livraison, on vérifie à la livraison que le produit livré figure bien dans la commande qui concerne la livraison; 
  - Toujours à l’insertion et à la modification d’une livraison, on vérifie que la quantité livrée du produit soit au plus égale à la quantité commandée concernant ce même produit (si la quantité est inférieure un avoir est créé, non géré ici mais cela ne pose pas de problème). 

Le 1er cas est réalisable avec des contraintes d'intégrité référentielle. Comment ?

Pour le 2nd cas il faudra des triggers.

<badge>A vous de jouer :</badge>
  - Proposer une évolution de la base de données pour gérer les livraisons
  - Modifier la base de données
  - Créer et tester le trigger SQL qui va comparer la quantité livrée à la quantité commandée.
 
====== Autres exemples ======
=====  Trigger pour vérifier une contrainte d’exclusion (gestion de classes) =====
{{:promo_2025:slam:contraintesexclusion.png?800|}}

Le couple (Année scolaire, Classe) constitue une promotion. C’est un agrégat. Une telle promotion dispose de plusieurs professeurs et élèves. En l’espèce, la contrainte d’exclusion figurant ci-dessus traduit une règle de gestion des plus simples : pour une classe et une année scolaire données, l’on ne peut être à la fois professeur et élève.  
 
==== Quels problèmes ? ====
En absence de traitement spécifique, rien ne nous assure qu’un professeur d’une promotion ne participe pas également à la relation élève et réciproquement. 

==== Quelle solution possible ? ====
Lorsque l’on ajoute un professeur, il convient qu’il ne figure pas parmi les élèves. Inversement, lorsque l’on ajoute un élève, il convient qu’il ne soit pas professeur. S’il participe aux deux relations il importe d’interdire l’ajout, à savoir l’insertion, voire la modification également. 
 
==== Quelle implémentation ? ====
**1er trigger : aucun professeur ne peut être élève.** 
 <code> 
 DROP TRIGGER IF EXISTS check_exclusion_prof ; 
 DELIMITER $$ 
 CREATE TRIGGER check_exclusion_prof BEFORE INSERT, UPDATE ON Professeur FOR EACH ROW  BEGIN 
  	– DECLARE QTE INT ;
  	-- si le professeur à ajouter/modifier existe en tant qu’élève, c’est mal ! 
  	IF ( SELECT COUNT(*) FROM Eleve E WHERE E.NumPersonne = NEW.NumPersonne ) > 0 THEN 
  	 	SIGNAL sqlstate '45000'  
  	 	 	SET message_text = 'Un professeur d’une promo ne saurait en être un élève' ; 
  	END IF ; 
 END $$ 
 DELIMITER ; 
 </code>

**2ème trigger : aucun élève ne peut être professeur. ** 

L’on crée le trigger check_exclusion_eleve dont la principale différence avec le précédent trigger est la requête SQL, laquelle devient : 
  
 SELECT COUNT(*) FROM Professeur P WHERE P.NumPersonne = NEW.NumPersonne 
   
====== 4) Historiser des données ======
L’historisation consiste à conserver, au travers du temps, l’historique des différentes modifications effectuées sur tout ou partie des champs d’une entité. 
__Exemple__ : historisation de l’état d’une tâche. 
{{:promo_2025:slam:historisation.png?800|}}
===== Quelle solution possible ? =====
A l’insertion ainsi qu’à la modification d’une tâche, on historise son état, à savoir qu’on crée une ligne d’historique.  
===== Quelle implémentation ? =====
<code>  
 DROP TRIGGER IF EXISTS historise_tache ; 
 DELIMITER $$ 
 CREATE TRIGGER historise_tache BEFORE INSERT, UPDATE ON Tache FOR EACH ROW  BEGIN 
  	-- crée une nouvelle ligne d’historique 
  	INSERT INTO Historique (NumTache, Num, Creation, NumEtat) 
  	VALUES ( 
  	 	NEW.Num,  
  	 	SELECT COUNT(*) + 1 FROM Historique WHERE NumTache = NEW.NUM,  
  	 	NOW(), 
  	 	NEW.NumEtat 
  	) ; 	 	 
 END $$ 
 DELIMITER ; 
</code>