Exercices du chapitre 6

Pour chacun des cas qui suit, déterminer comment faire respecter les contraintes d’intégrité en SQL pour le schéma PleinDeFoin. Donnez le code SQL correspondant. Supposez que la base de données ne contient pas encore de données. a) La quantité commandée ne peut être supérieure à 5 pour les Articles dont le noArticle est supérieur à 10000. b) Supposez qu’on ait ajouté à la table Commande une nouvelle colonne totalCommande. Le totalCommande doit être égal au total des montants de chacune des lignes de la commande. Le montant de chacune des lignes correspond à la quantité commandée multipliée par le prixUnitaire de l’Article. La modification des LigneCommandes et des prixUnitaire doit être interdite. c) Le prixUnitaire d’un Article ne peut diminuer. d) Supposez qu’on ait ajouté à la table Commande une nouvelle colonne totalCommande et à la table LigneCommande une nouvelle colonne totalLigne. Le totalCommande doit être égal au total des montants de chacune des lignes de la commande. Le totalLigne correspond à la quantité commandée multipliée par le prixUnitaire de l’Article.. La modification d’une LigneCommande est permise sauf lorsqu’il y a une ligne de DétailLivraison qui fait référence à LigneCommande. La modification du prixUnitaire est permise sous les mêmes conditions. e) Pour la question précédente, concevez un TRIGGER qui modifie directement le totalLigne des LigneCommande ainsi que le totalCommande des Commande suite à la modification du prixUnitaire de l’Article. f) Supposez qu’on ait ajouté à la table LigneCommande une nouvelle colonne quantitéEnAttente. La quantitéEnAttente d’une LigneCommande est égale à la quantité commandée moins le total des quantitéLivrées des DétailLivraison correspondant à la LigneCommande. La quantitéEnAttente doit être initialisée à la même valeur que la quantité commandée lors d’une insertion de LigneCommande. La quantitéEnStock doit être ajustée suite à l’insertion d’une ligne dans DétailLivraison. Il est interdit d’insérer une ligne dans DétailLivraison si la quantitéEnStock de l’Article est insuffisante ou si la quantitéLivrée dépasse la quantitéEnAttente de la LigneCommande. g) Il est interdit de supprimer une Commande s’il y a des LigneCommandes qui y font référence. h) La dateLivraison ne peut précéder la dateCommande. i) Une livraison ne touche toujours qu’un seul Client, c’est-à-dire ne peut être liée à des Commandes de plusieurs Clients.

CREATE TRIGGER

Solutions des exercices du chapitre 6 selon la syntaxe du dialecte Oracle 1. a) Ajouter un CHECK sur la table LigneCommande ALTER TABLE LigneCommande ADD (CONSTRAINT XXX CHECK (noArticle <= 10000 OR quantité <= 5)) b) CREATE TRIGGER initialiserTotalCommande BEFORE INSERT ON Commande FOR EACH ROW BEGIN :NEW.totalCommande:=0.0; END; CREATE TRIGGER modifierTotal AFTER DELETE OR INSERT ON LigneCommande FOR EACH ROW DECLARE prix NUMBER(10,2); BEGIN IF DELETING THEN LOCK TABLE Article IN ROW SHARE MODE; SELECT prixUnitaire INTO prix FROM Article WHERE noArticle = :OLD.noArticle; UPDATE Commande SET totalCommande = totalCommande – :OLD.quantité*prix WHERE noCommande = :OLD.noCommande ; END IF; IF INSERTING THEN LOCK TABLE Article IN ROW SHARE MODE; SELECT prixUnitaire INTO prix FROM Article WHERE noArticle = :NEW.noArticle; UPDATE Commande SET totalCommande = totalCommande + :NEW.quantité*prix WHERE noCommande = :NEW.noCommande ; END IF; END; CREATE TRIGGER empecherModificationLignes BEFORE UPDATE ON LigneCommande FOR EACH ROW BEGIN raise_application_error(-20100, ‘les lignes de commande ne peuvent être modifiées’); END; CREATE TRIGGER empecherModificationPrix BEFORE UPDATE OF prixUnitaire ON Article FOR EACH ROW BEGIN raise_application_error(-20101, ‘le prix ne peut être modifié’); END; c) CREATE OR REPLACE TRIGGER prixNePeutDiminuer BEFORE UPDATE OF prixUnitaire ON Article FOR EACH ROW WHEN (OLD.prixUnitaire > NEW.prixUnitaire) BEGIN raise_application_error(-20100, ‘le prix d »un produit ne peut diminuer’).

CREATE TRIGGER initialiser_total_commande BEFORE INSERT ON COMMANDES FOR EACH ROW BEGIN :NEW.total_commande:=0.0; END; CREATE OR REPLACE TRIGGER initialiserTotalLigne BEFORE INSERT ON LigneCommande FOR EACH ROW DECLARE totalLigne NUMBER(10,2); BEGIN LOCK TABLE Article IN ROW SHARE MODE; SELECT prixUnirtaire*:NEW.quantité INTO totalLigne FROM Article WHERE noArticle = :NEW.noArticle; :NEW.totalLigne:=totalLigne; END; CREATE OR REPLACE TRIGGER modifierTotalLigne BEFORE UPDATE of noCommande, quantité, noArticle ON LigneCommmande /*On ne peut intercepter les tentatives de modification de totalLigne car lorsque déclenché par le TRIGGER aprèsModificationPrix, il est interdit ici d’accéder au prix en cours de modification!*/ FOR EACH ROW DECLARE totalLigne NUMBER(10,2); nbLivraison INTEGER; BEGIN LOCK TABLE DétailLivraison IN SHARE MODE; SELECT COUNT(*) INTO nbLivraison FROM DétailLivraison WHERE noCommande = :OLD.noCommande AND noArticle = :OLD.noArticle ; IF nbLivraison = 0 THEN LOCK TABLE Article IN ROW SHARE MODE; SELECT prixUnitaire*:NEW.quantité INTO totalLigne FROM Article WHERE noArticle = :NEW.noArticle; :NEW.totalLigne:=totalLigne; ELSE raise_application_error(-20100, ‘il est interdit de modifier une ligne de commande lorsque des produits ont été livrés pour cette ligne’); END IF; END; CREATE OR REPLACE TRIGGER aprèsMajLignes AFTER DELETE OR INSERT OR UPDATE OF totalLigne, noCommande ON LigneCommande FOR EACH ROW BEGIN IF DELETING OR (UPDATING AND :OLD.noCommande != :NEW.noCommande) THEN UPDATE Commande SET totalCommande = totalCommande – :OLD.totalLigne WHERE noCommande = :OLD.noCommande ; END IF; IF INSERTING OR (UPDATING AND :OLD.noCommande != :NEW.noCommande) THEN UPDATE COMMANDES SET totalCommande = totalCommande + :NEW.totalLigne WHERE noCommande = :NEW.noCommande ; END IF; IF (UPDATING AND :OLD.noCommande = :NEW.noCommande AND :OLD.totalLigne != :NEW.totalLigne) THEN UPDATE Commande SET totalCommande = totalCommande – :OLD.totalLigne +:NEW.totalLigne WHERE noCommande = :NEW.noCommande ; END IF; END; CREATE OR REPLACE TRIGGER aprèsModificationPrix AFTER UPDATE OF prixUnitaire ON Article FOR EACH ROW DECLARE BEGIN UPDATE LigneCommande SET totalLigne = quantité*:NEW.prixUnitaire WHERE noArticle = :OLD.noArticle; END; e) CREATE TRIGGER aprèsModificationPrix AFTER UPDATE OF prixUnitaire ON Article FOR EACH ROW DECLARE.