Objectif
- Utiliser adéquatement les triggers en MySQL
Exercice 01
Énoncé
- Vous pouvez regarder l'énoncé de l'exercice
Solution
CREATE TRIGGER INS_CLIENT BEFORE INSERT ON CLIENT FOR EACH ROW DECLARE nom_conjoint CLIENT.NOM%TYPE ; compteur CLIENT.NUMCL%TYPE ; pb_dept EXCEPTION ; pb_conjoint1 EXCEPTION ; pb_conjoint2 EXCEPTION ; BEGIN -- Contrainte sur le département IF TRUNC(:NEW.CP/1000) NOT IN (01, 07, 26, 38, 42, 69, 73, 74) THEN RAISE pb_dept ; END IF ; -- Contrainte sur le nom du conjoint (+ test d’existence du conjoint) IF NEW.CONJOINT IS NOT NULL THEN SELECT COUNT(*), NOM INTO compteur, nom_conjoint FROM CLIENT WHERE NUMCL = :NEW.CONJOINT GROUP BY NOM ; IF compteur = 0 THEN -- Pas de conjoint RAISE pb_conjoint1 ; END IF ; IF nom_conjoint :NEW.NOM THEN RAISE pb_conjoint2 ; END IF ; END IF ; EXCEPTION WHEN pb_dept THEN RAISE_APPLICATION_ERROR (-20501, ‘Insertion impossible : le client n’habite pas en région Rhône-Alpes !’) ; WHEN pb_conjoint1 THEN RAISE_APPLICATION_ERROR (-20502, ‘Insertion impossible : le conjoint du client n’existe pas !’) ; WHEN pb_conjoint2 THEN RAISE_APPLICATION_ERROR (-20503, ‘Insertion impossible : le nom du conjoint est différent de celui du client !’) ; END ;
Exercice 02
Énoncé
- Vous pouvez regarder l'énoncé de l'exercice
Solution
Trigger01
DELIMITER // CREATE TRIGGER quest_1 AFTER INSERT ON Inscription FOR EACH ROW BEGIN DECLARE NB int; SELECT COUNT(*) INTO NB FROM Inscription; IF NB>12 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cours a un effectif maximum de 30 etudiants'; END IF; END //DELIMITER ;Trigger02
DELIMITER // CREATE TRIGGER quest_2 AFTER INSERT ON Cours FOR EACH ROW BEGIN DECLARE NB int; DECLARE SPEC int; SELECT specialite INTO SPEC FROM Professeur WHERE `id_professeur`=NEW.`id_professeur`; SELECT COUNT(*) INTO NB FROM Cours WHERE `id_professeur`=NEW.`id_professeur`; IF SPEC!='Mathématique' AND NB>3 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ="Seuls les professeurs du 'Mathématiques' enseignent plus de trois cours"; END IF; END //DELIMITER ;Trigger03
DELIMITER // CREATE TRIGGER quest_3 AFTER INSERT ON Eleve FOR EACH ROW BEGIN DECLARE NBINSC int; SELECT COUNT(*) INTO NBINSC FROM Inscription; INSERT INTO Inscription VALUES((NBINSC+1),148,NEW.Id_eleve,Now()); END //DELIMITER ;Trigger04
DELIMITER // CREATE TRIGGER quest_4 AFTER INSERT ON eleve FOR EACH ROW WHEN NEW.niveau_eleve='Base de données' OR NEW.niveau_eleve='Mathématiques' BEGIN DECLARE NB1 int; DECLARE NB2 int; SELECT COUNT(*) INTO NB1 FROM Eleve WHERE niveau_eleve='Base de données'; SELECT COUNT(*) INTO NB2 FROM Eleve WHERE niveau_eleve='Mathématiques'; IF NB2<NB1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ="Le nombre d eleves inscrits dans la section base de donnees doit être superieur au nombre d eleves dans la section Mathematiques."; END IF; END //DELIMITER ;Trigger05
DELIMITER // CREATE TRIGGER quest_5 BEFORE INSERT OR UPDATE ON Cours FOR EACH ROW BEGIN DECLARE DEP1 int; DECLARE NB int; SELECT id_professeur INTO DEP1 FROM Professeur WHERE id_professeur=NEW.id_professeur; SELECT count(*) INTO NB FROM Cours C INNER JOIN Professeur P ON C.`id_professeur`=P.`id_professeur` WHERE C.`salle_cours`=:NEW.`salle_cours` AND P.`id_professeur`!=DEP1; IF NB1>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ='Deux Courss ne peuvent pas se réunir dans la même salle en même temps.'; END IF; END; //DELIMITER ;
CREATE TABLE IF NOT EXISTS Eleve( Id_eleve INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom_eleve VARCHAR(7) NOT NULL, age_eleve INT(4) NOT NULL, niveau_eleve VARCHAR(30) NOT NULL, section_eleve VARCHAR(30) NOT NULL) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS Professeur( Id_professeur int(10) NOT NULL PRIMARY KEY, Nom_professeur VARCHAR(7) NOT NULL, specialite_professeur VARCHAR(100) NOT NULL) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS Cours( Id_cours int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom_cours VARCHAR(7) NOT NULL, heure_cours TIME NOT NULL, salle_cours VARCHAR(6) NOT NULL, id_professeur int(10) NOT NULL, FOREIGN KEY (id_professeur) REFERENCES Professeur(id_professeur) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS Inscription( Id_inscri int(11) NOT NULL PRIMARY KEY, Id_cours int(11) NOT NULL, Id_eleve INT(11) NOT NULL, date_inscri Date NOT NULL, FOREIGN KEY (Id_cours) REFERENCES cours(Id_cours) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (Id_eleve) REFERENCES eleve(Id_eleve) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB;Exercice 03
Énoncé
- Vous pouvez regarder l'énoncé de l'exercice
Solution
Trigger01 : trig-before-insert-client
Trigger01 : trig-before-update-client
Trigger02
DELIMITER // CREATE TRIGGER `before-insert-avis` BEFORE INSERT ON `AVIS` FOR EACH ROW BEGIN IF (NEW.dateAvis > CURRENT_TIMESTAMP) THEN SET NEW.dateAvis = CURRENT_TIMESTAMP ; END IF ; END // DELIMITER ;
Table:clientCREATE TABLE client( IDC INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, IDClient VARCHAR(20) NOT NULL, NomClient varchar(20) DEFAULT NULL, Segment varchar(11) DEFAULT NULL, Ville varchar(16) NOT NULL, Region VARCHAR(100) NOT NULL, Pays VARCHAR(100) NOT NULL, ZoneGeographique VARCHAR(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Table:produitCREATE TABLE `produit` ( `IDProduit` varchar(30) NOT NULL, `NomProduit` varchar(109) DEFAULT NULL, `SousCategorie` varchar(11) DEFAULT NULL, `Categorie` varchar(15) DEFAULT NULL, PRIMARY KEY (`IDProduit`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Table:ventesCREATE TABLE `ventes` ( `IDLigne` int(11) NOT NULL, `IDProduit` varchar(30) NOT NULL, `IDC` int(11) NOT NULL, `DateDeCommande` varchar(16) DEFAULT NULL, `DateExpedition` varchar(16) DEFAULT NULL, `IDCommande` varchar(14) DEFAULT NULL, `ModeExpedition` varchar(14) DEFAULT NULL, `MontantVente` decimal(7,4) DEFAULT NULL, `Quantite` int(2) DEFAULT NULL, `Profit` decimal(9,4) DEFAULT NULL, `Remise` decimal(4,2) DEFAULT NULL, PRIMARY KEY (`IDLigne`,`IDProduit`,`IDC`), FOREIGN KEY (`IDProduit`) REFERENCES `produit`(`IDProduit`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`IDC`) REFERENCES `client`(IDC) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Table:avisCREATE TABLE `avis` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `IDC` int(11) NOT NULL, `IDProduit` varchar(30) NOT NULL, `dateAvis` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `note` enum('1','2','3','4','5') NOT NULL, `commentaire` text, PRIMARY KEY (`ID`), FOREIGN KEY (`IDProduit`) REFERENCES `produit`(`IDProduit`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`IDC`) REFERENCES `client`(IDC) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=526 DEFAULT CHARSET=utf8;DELIMITER // CREATE TRIGGER `trig-before-insert-client` BEFORE INSERT ON `CLIENT` FOR EACH ROW BEGIN IF NEW.Segment IS NULL THEN SET NEW.Segment = 'Inconnu' ; END IF ; SET NEW.NomClient = UPPER(NEW.NomClient); END // DELIMITER ;DELIMITER // CREATE TRIGGER `trig-before-update-client` BEFORE UPDATE ON `CLIENT` FOR EACH ROW SET NEW.NomClient = UPPER(NEW.NomClient) ; // DELIMITER ;Exercice 04
Énoncé
- Vous pouvez regarder l'énoncé de l'exercice
Solution
-
- Mais ce trigger ne suffit pas à lui seul : il faut en écrire un autre ayant le même code, en cas de modification de la colonne NUM_ID.
DELIMITER // CREATE TRIGGER `before-insert-s_news.num` BEFORE INSERT ON S_NEWS.NUM FOR EACH ROW BEGIN IF (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM S_NEWS.NUM WHERE COALESCE(NUM_ID, 0) <> 0 GROUP BY NUM_ID)) > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ="Violation de la contrainte d'\nunicité"; END IF; END// DELIMITER ;DELIMITER // CREATE TRIGGER `before-insert-s_news.num` BEFORE UPDATE ON S_NEWS.NUM FOR EACH ROW .... DELIMITER ; -
DELIMITER // CREATE TRIGGER `after-delete-employe` AFTER DELETE ON EMPLOYE FOR EACH ROW BEGIN IF (select count(*)from deleted )>50 begin rollback SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ="Ne pas supprimer plus de 50 enregistrement"; end END DELIMITER ;
-