Bases de Données / Databases

Site Web de l'équipe BD du LIP6 / LIP6 DB Web Site

Outils pour utilisateurs

Outils du site

Piste :
site:enseignement:licence:2i009:tme_triggers

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Prochaine révision		Révision précédente
site:enseignement:licence:2i009:tme_triggers [04/12/2018 14:06] – créée amannsite:enseignement:licence:2i009:tme_triggers [15/04/2019 19:24] (Version actuelle) – [Rappel PL/SQL] amann
Ligne 2: Ligne 2:
  
 ====== TME 11 Triggers ====== ====== TME 11 Triggers ======
-Sous Oracle, le bloc d’instructions d’un trigger est un bloc PL/SQL.  +Sous Oracle, le bloc d’instructions d’un trigger est un bloc PL/SQL. Dans ce TME nous allons utiliser un sous-ensemble minimal de PL/SQL pour définir et modifier des variables 
-Dans ce TME nous allons utiliser un sous-ensemble minimal de PL/SQL pour définir et modifier des variables +
 et exécuter des ordres SQL. et exécuter des ordres SQL.
  
-======= Rappel de la syntaxe PL/SQL =======+======= Rappel PL/SQL =======
  
 **Bloc PL/SQL** **Bloc PL/SQL**
  
-La syntaxe d'un bloc PL/SQL est rappelée   ci-dessous.+La syntaxe d'un bloc PL/SQL est rappelée ci-dessous.
  
 <code> <code>
Ligne 33: Ligne 32:
 On utilise l’opérateur '':='' pour affecter une valeur à une variable, lors de sa déclaration ou dans une instruction de la section BEGIN. On peut donc y introduire tous les éléments du langage PL/SQL, et notamment des variables, déclarées dans une section ''DECLARE'', juste avant la section ''BEGIN…END''. On utilise l’opérateur '':='' pour affecter une valeur à une variable, lors de sa déclaration ou dans une instruction de la section BEGIN. On peut donc y introduire tous les éléments du langage PL/SQL, et notamment des variables, déclarées dans une section ''DECLARE'', juste avant la section ''BEGIN…END''.
  
-Instructions PL/SQL :+**Instructions PL/SQL**
  
-Une expression SQL est une instruction PL/SQL. Par exemple, on peut utiliser l'ordre UPDATE pour modifier la base de données. On suppose que chaque requête SQL retourne au maximum un nuplet. La valeur de cet nuplet peut être copiée dans des variables déclarées dans la section DECLARE. Par exemple :+Une expression SQL est une instruction PL/SQL. Par exemple, on peut utiliser l'ordre UPDATE pour modifier la base de données. On suppose que chaque requête SQL retourne au maximum un nuplet. La valeur de ce nuplet peut être copiée dans des variables déclarées dans la section DECLARE. Par exemple :
  
 <code sql> <code sql>
Ligne 56: Ligne 55:
 Les commandes exit et exit when <cond> permettent de sortir d'une boucle. Les commandes exit et exit when <cond> permettent de sortir d'une boucle.
  
-**Exceptions :**+**Exceptions**
  
 +Un trigger peut détecter une situation où il faut annuler une transaction (une transaction est une séquence de lectures et de mises-à-jour qui sont exécutées ou annulées ensemble).
 On ne peut pas annuler une transaction à l'intérieur d'un trigger mais on peut déclencher une exception en utilisant la fonction RAISE_APPLICATION_ERROR (qui peut ensuite être traitée par la procédure qui a déclenché le trigger) : On ne peut pas annuler une transaction à l'intérieur d'un trigger mais on peut déclencher une exception en utilisant la fonction RAISE_APPLICATION_ERROR (qui peut ensuite être traitée par la procédure qui a déclenché le trigger) :
  
Ligne 64: Ligne 64:
 </code> </code>
  
 +Le code d'une exception utilisateur doit être compris entre -20999 et -20000.
  
 ** Limitations : ** ** Limitations : **
 Un trigger AFTER déchenché après un update (ou un insert) de la table T n'est pas autorisé à modifier la table T (ne pas écrire d'instruction ''update T'' dans le corps du trigger). Pour palier cette limitation, utiliser un trigger BEFORE. Un trigger AFTER déchenché après un update (ou un insert) de la table T n'est pas autorisé à modifier la table T (ne pas écrire d'instruction ''update T'' dans le corps du trigger). Pour palier cette limitation, utiliser un trigger BEFORE.
- 
  
  
Ligne 90: Ligne 90:
 DROP TABLE INSCRIPTION; DROP TABLE INSCRIPTION;
 CREATE TABLE  INSCRIPTION (   CREATE TABLE  INSCRIPTION (  
- noEtudiant smallint, + noEtud smallint, 
   noTD smallint,    noTD smallint, 
   codeUE varchar(10),   codeUE varchar(10),
- PRIMARY KEY (noEtudiant, codeUE)) ;+ PRIMARY KEY (noEtud, codeUE)) ;
 </code> </code>
  
 Exécutez les instructions de création de tables ci-dessus dans le client Oracle (sqlplus). Exécutez les instructions de création de tables ci-dessus dans le client Oracle (sqlplus).
-({{:site:enseignement:licence:3i009:tme_7_schema.sql|}})+({{ :site:enseignement:licence:2i009:tme_11_schema.sql |}})
  
 La clé primaire de relation TD est (noTD, codeUE) et celle de la relation INSCRIPTION est (noEtud, codeUE). La clé primaire de relation TD est (noTD, codeUE) et celle de la relation INSCRIPTION est (noEtud, codeUE).
Ligne 108: Ligne 108:
  
 ======= Questions ======= ======= Questions =======
-Pour chacune des questions suivantes, vérifier que le trigger est créé +Pour chacune des questions suivantes, vérifier que le trigger est créé sans erreurs et proposer des insertions/suppressions/mises à jour pour vérifier qu'il fonctionne correctement.
-sans erreurs et qu'il fonctionne en proposant une insertion/suppression/mise à jour +
-dans la table concernée. +
- +
-  - Créer un trigger qui assure que les valeurs des attributs NIVEAU et CODEUE, entrées dans la table TD, soient en majuscules, quelle que soit la casse utilisée lors des instructions d’insertion ou de mise à jour (utilisez la fonction UPPER).  +
-  - Créez un trigger qui empêche qu'un étudiant s'inscrive dans plus que 6 UEs.  +
-  - Créez un trigger qui empêche que le nombre de groupes par UE dépasse 4. +
-  - Créez un trigger qui empêche le nombre d'étudiants dans chaque groupe de dépasser  32. +
-  - Pour empêcher un étudiant de s’inscrire à des TD incompatibles (i.e. ayant lieu en même temps),  on introduit une nouvelle contrainte d’intégrité dans la base : un étudiant ne peut pas avoir deux inscriptions qui lui imposent d’être, au même moment, à deux endroits différents.+
  
 +==== Question 1 ====
 +Créer deux triggers qui assurent que les valeurs des attributs NIVEAU et CODEUE, entrées dans la table TD et INSCRIPTION, soient en majuscules, quelle que soit la casse utilisée dans les instructions d’insertion ou de mise à jour (utilisez la fonction UPPER). Vérifiez si les triggers sont corrects.
 +==== Question 2 ====
 + Créez un trigger qui empêche qu'un étudiant s'inscrive dans plus que 6 UEs. 
 +==== Question 3 ====
 +Créez un trigger qui empêche que le nombre de groupes de TD par UE dépasse 4.
 +==== Question 4 ====
 +Créez un trigger qui empêche qu’un étudiant suive deux TDs enseignés par le même enseignant.
 +==== Question 5 ====
 +Pour empêcher un étudiant de s’inscrire à des TD incompatibles (i.e. ayant lieu en même temps),  on introduit une nouvelle contrainte d’intégrité dans la base : un étudiant ne peut pas avoir deux inscriptions qui lui imposent d’être, au même moment, à deux endroits différents.
 Ecrire un trigger qui assure cette contrainte, de la façon suivante : Ecrire un trigger qui assure cette contrainte, de la façon suivante :
   * en préambule à chaque inscription, le trigger inscrit le numéro de l’étudiant, avec le jour, l’heure et la salle du TD qu’il a choisi, dans une table supplémentaire LOCETUD  et une contrainte d’intégrité qui assure l’unicité du triplet (NoETUDIANT, JOUR, HEURE):   * en préambule à chaque inscription, le trigger inscrit le numéro de l’étudiant, avec le jour, l’heure et la salle du TD qu’il a choisi, dans une table supplémentaire LOCETUD  et une contrainte d’intégrité qui assure l’unicité du triplet (NoETUDIANT, JOUR, HEURE):
Ligne 129: Ligne 131:
 </code>   </code>  
   * si l’étudiant a déjà pris une inscription correspondant au même jour et à la même heure, la contrainte de table précédente provoque le rejet du nouveau quadruplet  (NoETUDIANT, JOUR, HEURE, SALLE), le trigger est arrêté sur erreur, et l’instruction d’insertion d’un nouvel élément dans la table INSCRIPTION est, par suite, arrêtée sur erreur.    * si l’étudiant a déjà pris une inscription correspondant au même jour et à la même heure, la contrainte de table précédente provoque le rejet du nouveau quadruplet  (NoETUDIANT, JOUR, HEURE, SALLE), le trigger est arrêté sur erreur, et l’instruction d’insertion d’un nouvel élément dans la table INSCRIPTION est, par suite, arrêtée sur erreur. 
 +
 +==== Question 6 (optionnel) ====
 +Trouvez une solution pour la question précédente qui évite d’introduire la table supplémentaire LOCETUD. Conseil : il faut faire une jointure sur les tables TD et INSCRIPTION.
  
        
  
site/enseignement/licence/2i009/tme_triggers.1543928819.txt.gz · Dernière modification : de amann