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 et exécuter des ordres SQL.
Bloc PL/SQL
La syntaxe d'un bloc PL/SQL est rappelée ci-dessous.
[ DECLARE -- déclaration de variables ] BEGIN -- instructions PL/SQL END;
où les les crochets '[' et ']' délimitent les éléments optionnels.
Variables PL/SQL
Les variables utilisées dans un bloc PL/SQL doivent être déclarées dans la section DECLARE de ce bloc.
Elles peuvent prendre n’importe quel type du langage SQL.
L’attribut %TYPE permet de faire référence au type d’une colonne donnée.
Affectation de variables PL/SQL
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
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 :
SELECT Jour, Heure, Salle INTO JourI, HeureI, SalleI FROM TD WHERE niveau=:NEW.niveau AND UE=:NEW.UE AND NoTD=:NEW.NoTD;
On peut également appliquer des fonctions de conversion pour modifier les valeurs de variables ; Par exemple les fonctions UPPER et LOWER transforment leur arguments (des chaînes de caractères) respectivement en majuscule et minuscule. Il est possible d'utiliser les structures de contrôle suivantes :
IF (<condition>) THEN <bloc_psql1> [ ELSE <bloc_plsql2> ] END IF; LOOP <bloc_psql> END LOOP; FOR <var> IN <seq> LOOP <bloc_psql> END LOOP ; WHILE <condition> LOOP <bloc_psql> END LOOP ;
Les commandes exit et exit when <cond> permettent de sortir d'une boucle.
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) :
RAISE_APPLICATION_ERROR(code,'message') ;
Le code d'une exception utilisateur doit être compris entre -20999 et -20000.
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.
On considère une base de données avec des informations sur les enseignements, les enseignants et les étudiants. On s'intéresse plus particulièrement au schéma suivant
DROP TABLE TD; CREATE TABLE TD ( noTD SMALLINT NOT NULL, codeUE VARCHAR(10), niveau VARCHAR(10), salle VARCHAR(10), jour VARCHAR(8) NOT NULL, heure VARCHAR(5) NOT NULL, noEns SMALLINT, PRIMARY KEY ( NoTD, codeUE ), UNIQUE (salle,jour,heure)) ; DROP TABLE INSCRIPTION; CREATE TABLE INSCRIPTION ( noEtud SMALLINT, noTD SMALLINT, codeUE VARCHAR(10), PRIMARY KEY (noEtud, codeUE)) ;
Exécutez les instructions de création de tables ci-dessus dans le client Oracle (sqlplus). (tme_11_schema.sql)
La clé primaire de relation TD est (noTD, codeUE) et celle de la relation INSCRIPTION est (noEtud, codeUE). Chaque n-uplet de la relation TD renseigne sur un TD d’une UE donnée, pour un niveau donné. (ex. le TD 2 de l'UE BD2 du niveau L3). Les TD ont lieu au rythme d’une fois par semaine, pendant toute l’année universitaire. Les attributs SALLE, JOUR et HEURE donnent le lieu, le jour et l’horaire de début du TD. On suppose que tous les TDs de toutes les UEs sont synchronisés au niveau des horaires et de la durée (il n'y a pas de conflit possible entre deux TDs qui ne commencent pas à la même heure et n'ont pas lieu dans la même salle). L’enseignant qui assure le TD est identifié par NOENS. Les étudiants s’inscrivent à chaque UE, séparément, et choisissent un seul TD pour cette UE. L'étudiant est identifié par NOETUD.
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.
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.
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 de TD par UE dépasse 4.
Créez un trigger qui empêche qu’un étudiant suive deux TDs enseignés par le même enseignant.
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 :
CREATE TABLE LOCETUD ( NoEtud SMALLINT, Jour VARCHAR(8), Heure VARCHAR(5), Salle VARCHAR(10), PRIMARY KEY ( NoEtud, Jour, Heure ));
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.