L3 ISIL A Module Base de données (BD2) Octobre 2024
N.ABDAT
TP N°3 : Contraintes d'intégrité dynamiques / Triggers
-Se connecter avec l’utilisateur créé pour le TP BD2.
-Lancer le script de suppression des tuples des tables sauf DEPARTEMENT (c’est-à-dire vider les tables
EMPLOYE, PROJET, PARTICIPER).
-Ajout de nouvelles informations : ajouter les attributs (type entier) comme indiqué dans le tableau suivant
Relation Attribut Description Valeur par défaut
EMPLOYE Proj nombre de projets auxquels a participé l’employé 0
DEPARTEMENT Emp nombre d'employés travaillant dans le département 0
Proj nombre de projets réalisés par le département
PROJET Emp nombre d'employés participant à la réalisation du projet 0
-Définitions des déclencheurs :
1- On veut définir un déclencheur Emp_Dep qui permet de mettre à jour le nombre d'employés travaillant dans un
département dans le cas de l’insertion d’un nouvel employé. Donner la commande de création de ce trigger.
-Vérifier par l'exécution des commandes suivantes:
- Afficher la table DEPARTEMENT
-Insérer les tuples de EMPLOYE. Afficher DEPARTEMENT pour vérifier que Emp a été correctement mis à jour.
2- Définir un trigger Proj_Dep qui permet à chaque insertion d'un tuple dans la table Projet, d'incrémenter Proj dans
la table DEPARTEMENT.
-Vérifier par l'exécution des commandes suivantes:
-Afficher la table DEPARTEMENT
-Insérer les tuples de la table PROJET
-Afficher la table DEPARTEMENT pour vérifier que Proj a été correctement mis à jour.
3- Définir un trigger Part_Emp_Proj qui permet à chaque insertion/suppression d'un tuple dans PARTICIPER,
d'incrémenter/décrémenter Emp dans PROJET et d'incrémenter/décrémenter Proj dans EMPLOYE .
-Insérer les tuples de PARTICIPER puis afficher PROJET(CodeP, Emp) et EMPLOYE(NumE, Proj) pour vérifier
que les mises à jour ont été correctement effectuées.
-Supprimer de PARTICIPER le tuple (2,'P02') puis afficher PROJET(CodeP, Emp) et EMPLOYE(NumE, Proj) pour
vérifier.
4- On veut modifier le trigger Emp_Dep défini dans la question 1, pour ajouter le cas où un employé change de
DEPARTEMENT. Décrire dans ce cas, l'évènement ainsi que l’action à déclencher.
-Donner la commande permettant de modifier le trigger Emp_Dep
-Vérifier par l'exécution successive des commandes suivantes:
-L'employé N°1 change de département (vers D04). Ecrire la commande puis afficher DEPARTEMENT
5- Soit la contrainte "on ne peut pas réduire le budget d'un projet", c’est-à-dire qu'en cas de modification, le budget
ne peut qu'augmenter. Ecrire un trigger permettant de définir cette contrainte dynamique en utilisant la procédure
Système RAISE_APPLICATION_ERROR pour gérer le cas de tentative de réduire le budget. Afficher dans ce
cas 'impossible de réduire le budget'. (ci-dessous la syntaxe de cette procédure)
(on peut utiliser la clause WHEN dans ce trigger).
Syntaxe : RAISE_APPLICATION_ERROR (Num_erreur, Message) ; Où :
• Num_erreur : code d’erreur donné par l’utilisateur (compris entre -20999 et -20000).
• Message : est le texte à afficher décrivant l’erreur.
Remarque :
-L’utilisation de RAISE_APPLICATION_ERROR dans la section des instructions d’un
trigger permet d’annuler l’instruction qui le déclenche.
TP N° 3 1
L3 ISIL A Module Base de données (BD2) Octobre 2024
N.ABDAT
Manuel TP Oracle : Les déclencheurs (TRIGGERS)
Valeur par défaut : Commande pour ajouter un attribut avec une valeur par défaut :
ALTER TABLE nomTable ADD (nomAttribut typeAttribut DEFAULT valeur) ;
Les déclencheurs:
a) Syntaxe de création/modification d’un trigger :
CREATE [OR REPLACE ] TRIGGER nom-trigger
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column ...]}
[OR {DELETE | INSERT | UPDATE [OF column ...]}] ..
ON nom-table
[ FOR EACH ROW ]
[WHEN (condition)] ]
Begin
instructions ;
End ;
/
Où :
OR REPLACE : modifier un trigger existant.
nom-trigger : nom du trigger.
BEFORE : le trigger est déclenché avant d’exécuter l’événement.
AFTER : le trigger est déclenché après l’exécution de l’événement.
DELETE, INSERT, UPDATE...OF : événement déclenchant le trigger
ON nom-table : nom de la table de l’événement.
FOR EACH ROW : le trigger est exécuté autant de fois qu'il y a de tuples (enregistrements) touchés par
l’événement (row trigger). Sinon, s’il n’y a pas cette clause alors une seule exécution sera réalisée pour tout le
groupe de tuples (enregistrements) concernés (statement trigger).
WHEN : spécifie une condition qui doit être vérifiée pour déclencher le trigger (valable uniquement pour un row
trigger. Oracle évalue la condition pour chaque ligne touchée par le trigger)
Instructions : actions (ordres sql) à exécuter.
Le code est terminée par un slash "/"
b) Trigger avec plusieurs évènements :
CREATE [OR REPLACE] TRIGGER nom-trigger
{BEFORE | AFTER} INSERT OR DELETE OR UPDATE OF nomAttribut ON nom-table
FOR EACH ROW
BEGIN
IF INSERTING THEN .............instructions........................ END IF;
IF DELETING THEN ............. instructions....................... END IF;
IF UPDATING THEN ............. instructions....................... END IF;
END;
/
TP N° 3 2
