PLAN DU COURS Base de données avancées

• Chapitre 1. Architecture des SGBD et généralité sur les BD:

• Architecture des SGBD
• Contraintes d’intégrité
• Méta-base
• Mémoire relationnelle
• Chapitre 2. Le langage PL/SQL
• Chapitre 3. Les Triggers
• Chapitre 4. Les transactions
• Chapitre 5. Optimisation et évaluation des requêtes centralisées
• Chapitre 6. BD Distribuées : Fragmentation, allocation et optimisation
• Chapitre 7. Les BDs non relationnelles (NoSQL)
[Link]. HPC M1 BDA USTHB 1
Les triggers

[Link]. HPC M1 BDA USTHB 2

• Contraintes d’intégrité dynamiques :

• Les contraintes d’intégrité dynamiques sont celles qui concernent le

passage d’un état à un autre.
• En d’autres termes, les n-uplets du nouvel état dépendant de ceux de
l’ancien.

[Link]. HPC M1 BDA USTHB 3

• Variables OLD et NEW :

• Pour exprimer une contrainte dynamique sur les valeurs, il faut pouvoir dans le langage
relationnel faire une référence explicite à l’ancienne valeur (OLD) et à la valeur Nouvelle (New).

• Par exemple, [Link] ≥ [Link] exprime le fait que le salaire d’un employé ne peut
diminuer, cette vérification doit se faire après chaque modification du salaire.

• Remarquons ici que l’on indique le moment où cette contrainte doit être vérifiée, à savoir lors
d’une mise à jour.

• Un autre aspect des contraintes d’intégrité dynamiques est celui qui consiste à définir des actions
spontanées qui seront déclenchées automatiquement par le SGBD lorsque certains éléments se
produisent. Ceci est assuré par les déclencheurs.

[Link]. HPC M1 BDA USTHB 4

Trigger
• Définition : Les déclencheurs (ou triggers) sont des ordres de
déclenchement d’opérations quand un événement survient sur une
table.
• Ils sont utilisés pour assurer la cohérence des données dans la base
en réalisant des contraintes qui doivent porter sur plusieurs tables.

[Link]. HPC M1 BDA USTHB 5

Trigger
• CREATE [OR REPLACE ] TRIGGER trigger_name : pour créer ou remplacer un trigger existant.
• {BEFORE | AFTER} : le moment du déclenchement du trigger (avant ou après l’opération de mise
à jour).
• {INSERT [OR] | UPDATE [OR] | DELETE} : l’événement de mise à jour qui provoquera le
déclenchement du trigger. Plusieurs événements séparés par OR sont possibles.
• [OF col_name] : utilisé dans le cas de l’opération Update appliquée sur une colonne particulière.
• [ON table_name] : le nom de la table sur laquelle le trigger est défini.
• [FOR EACH ROW] : spécifie si le trigger est lancé pour chaque ligne affecté ou une seule fois.
• WHEN (condition) : le trigger est lance seulement lorsque la ligne affectée vérifie la condition.
• Remarque : pour générer une exception et empêcher le programme de continuer, l’utilisateur peut
lancer la procédure raise_application_error (-Num_Message, 'Message à Afficher’) ;
Num_Message est compris entre 20000 et 20999.

[Link]. HPC M1 BDA USTHB 6

Trigger
• CREATE [OR REPLACE ] TRIGGER trigger_name
• {BEFORE | AFTER }
• {INSERT [OR] | UPDATE [OR] | DELETE}
• [OF col_name]
• ON table_name
• [FOR EACH ROW]
• WHEN (condition)
• BEGIN
• --- Instruction PLSQL
• END;

[Link]. HPC M1 BDA USTHB 7

Exemple
• Exemple : Soit le schéma relationnel suivant
• Département (Ndep, NomDep, NbEmp)
• Employé (Numemp, NOM, Salaire, Ndep*)
•
• On veut exprimer que lorsqu’un nouvel employé est inséré dans la base, le nombre
d’employés figurant dans la relation Département doit augmenter de un pour le
département du nouvel employé.

• CREATE OR REPLACE TRIGGER EMPINS

• AFTER INSERT ON Employé
• For each row
• Begin
• UPDATE Département SET NbEmp = NbEmp + 1 WHERE Ndep= :[Link];
• End;

[Link]. HPC M1 BDA USTHB 8

• Département (Ndep, NomDep, NbEmp)
• (1,’A’, 3)
• (2,’B’,1)+1
• (3,’C’,2)
• Employé (Numemp, NOM, Salaire, Ndep*)
• (1, …, …, 2)
• (2, …, …, 3)
• (3, …, …, 1)
• (4, …, …, 1)
• (5, …, …, 1)
• (6, …, …, 3)
• (7,…,…, 2)
• ([Link],[Link],[Link],[Link])
•

[Link]. HPC M1 BDA USTHB 9

Exemple
• Pour que la base soit toujours cohérente, il faut également décrire ce qui doit se
passer lors d’une suppression d’un employé.
•
• On aura donc :
•
• CREATE OR REPLACE TRIGGER EMPSUP
• AFTER DELETE ON Employé
• For each row
• begin
• UPDATE Département SET NbEmp = NbEmp – 1 WHERE Ndep= :[Link];
• End;

[Link]. HPC M1 BDA USTHB 10

• Département (Ndep, NomDep, NbEmp)
• (1,’A’, 3)
• (2,’B’,2)
• (3,’C’,2)-1
• Employé (Numemp, NOM, Salaire, Ndep*)
• (1, …, …, 2)
• (2, …, …, 3)
• (3, …, …, 1)
• (4, …, …, 1)
• (5, …, …, 1)
• (6, …, …, 3)
• ([Link],[Link],[Link],[Link])
• (7,…,…, 2)

[Link]. HPC M1 BDA USTHB 11

• Remarque :
•
• Si l’événement est une modification (update), alors
• NEW : variable contenant la valeur de l’enregistrement concerné
après sa mise à jour.
• OLD : variable contenant la valeur de l’enregistrement concerné avant
sa mise à jour.

[Link]. HPC M1 BDA USTHB 12

• Si l'instruction de déclenchement du trigger est INSERT, seule la
nouvelle valeur a un sens (:[Link]).

• Si l'instruction de déclenchement du trigger est DELETE, seule

l'ancienne valeur a un sens (:[Link]).

[Link]. HPC M1 BDA USTHB 13

• Quand un trigger comporte plusieurs instructions de déclenchement (par exemple INSERT OR DELETE OR
UPDATE), on peut utiliser des prédicats conditionnels (INSERTING, DELETING et UPDATING) pour exécuter
des blocs de code spécifiques pour chaque instruction de déclenchement.

• Exemple

• CREATE TRIGGER ... BEFORE INSERT OR UPDATE ON employe ....... BEGIN ......

• IF INSERTING THEN ....... END IF;

• IF UPDATING THEN ........ END IF;

• ...... END;

[Link]. HPC M1 BDA USTHB 14

• CREATE OR REPLACE TRIGGER EMPtrigger
• AFTER INSERT OR DELETE ON Employé
• For each row
• Begin
• If inserting then UPDATE Département SET NbEmp = NbEmp + 1 WHERE Ndep=
:[Link];
• End if;
• If deleting then UPDATE Département SET NbEmp = NbEmp – 1 WHERE Ndep=
:[Link];
• End if;
• End;
• /
[Link]. HPC M1 BDA USTHB 15
• UPDATING peut être suivi d'un nom de colonne :

• CREATE TRIGGER ... BEFORE UPDATE OF salaire ON employe .......

• BEGIN ......

• IF UPDATING ('salaire') THEN ........

• END IF;

• ...... END;

[Link]. HPC M1 BDA USTHB 16

• il faut avoir le privilège CREATE TRIGGER

• il faut soit posséder la table sur laquelle on veut définir un trigger, soit posséder
le privilège ALTER sur la table sur laquelle on veut définir le trigger, soit
posséder le privilège ALTER ANY TABLE

• Modification de triggers
• Pour modifier un trigger, on refait une instruction CREATE TRIGGER suivie de OR REPLACE
ou bien on supprime le trigger (DROP TRIGGER nomtrigger) et on le crée à nouveau.

[Link]. HPC M1 BDA USTHB 17

• Un trigger peut être activé ou désactivé.
• S’il est désactivé, ORACLE le stocke mais l’ignore.

• On peut désactiver un trigger si on veut charger rapidement un volume de données important ou recharger des données déjà contrôlées.

• Par défaut, un trigger est activé dès sa création.

• Pour désactiver un trigger, on utilise l’instruction

• ALTER TRIGGER avec l’option DISABLE : ALTER TRIGGER nomtrigger DISABLE;

• On peut désactiver tous les triggers associés à une table avec la commande :
• ALTER TABLE nomtable DISABLE ALL TRIGGERS;

• A l’inverse on peut réactiver un trigger :

• ALTER TRIGGER nomtrigger ENABLE; ou tous les triggers associés à une table : ALTER TABLE nomtable ENABLE ALL TRIGGERS;

[Link]. HPC M1 BDA USTHB 18

• Les définitions des triggers sont stockées dans les tables de la
métabase, notamment dans les tables USER_TRIGGERS,
ALL_TRIGGERS et DBA_TRIGGERS

• Un bloc PL/SQL dans un trigger peut contenir un bloc EXCEPTION

gérant les différentes erreurs possibles avec des clauses WHEN.

[Link]. HPC M1 BDA USTHB 19

• grille(grade,salmin,salmax)

• employe(numemp,salaire,grade*,...)

• /* vérifier le salaire d'un employé , s'assurer que le salaire est compris dans
les bornes correspondant au grade de l'employé */

[Link]. HPC M1 BDA USTHB 20

CREATE OR REPLACE TRIGGER verif_grade_salaire BEFORE INSERT OR UPDATE OF salaire, grade ON
employe
FOR EACH ROW
DECLARE minsal [Link]%type; maxsal [Link]%type;
BEGIN /* retrouver le salaire minimum et maximum du nouveau grade ajouté dans emplyé*/
SELECT salmin,salmax INTO minsal, maxsal FROM grille WHERE grade= :[Link];
/* s'il y a un problème, on provoque une erreur */
IF (:[Link]<minsal OR :[Link]>maxsal) THEN raise_application_error (-20300,'Salaire‘||
:[Link]|| 'incorrect pour ce grade');
EXCEPTION WHEN no_data_found THEN raise_application_error(-20301,'Grade incorrect');
END;

[Link]. HPC M1 BDA USTHB 21

• grille(grade,salmin,salmax)
• (1, 30000, 50000)
• (2, 60000, 70000)
• (3, 80000, 90000) minsal, maxsal

• employe(numemp,salaire,grade*,...)
• (1, 40000, 1)
• (2, 65000, 2)
• (3, 65000, 2)
• (4, 80000, 3)

• (2, 80000, 4) update employe set salaire = 70000 and grade =4 where
numemp =2;
• ([Link], [Link], [Link])
[Link]. HPC M1 BDA USTHB 22
employe(….,numserv*)

service(numserv,...)

/* vérifier que le service de l'employé existe bien */

[Link]. HPC M1 BDA USTHB 23

service(numserv,...)

(1,….)

(2,….)

(3,….)

employe(….,numserv*)

(1,….,1)

(2,….,2)

(3,….,2)

(4,….,1)

(5,….,4)

([Link],……,[Link])

[Link]. HPC M1 BDA USTHB 24

CREATE TRIGGER verif_service BEFORE INSERT OR UPDATE OF numserv ON employe

FOR EACH ROW

DECLARE noserv [Link]%TYPE;

BEGIN

SELECT numserv INTO noserv FROM SERVICE WHERE numserv=:[Link];

EXCEPTION

When no_data_found THEN raise_application_error(-20501, 'N° de service n’’existe pas dans la table');
END;
/

[Link]. HPC M1 BDA USTHB 25

service(numserv,...)

employe(….,numserv*)

/* vérifier que le service de l'employé existe bien */

CREATE TRIGGER verif_service BEFORE INSERT OR UPDATE OF numserv ON employe

FOR EACH ROW

DECLARE

noserv [Link]%TYPE := 0;

Error exception;

BEGIN

SELECT numserv INTO noserv FROM SERVICE WHERE numserv=:[Link];

If (norserv <> 0) then dbms_output.put_line('ce service ' || noserv || ' existe bien dans la table service, il peut alors être mis à jour');

Else raise Error;

End if;

Exception

When Error then raise_application_error(-20501, 'N° de service non correct');

END; [Link]. HPC M1 BDA USTHB 26
On peut utiliser un curseur pour récupérer tous les num service et
comparer jusqu’à trouver la correspondance avec [Link]
• CREATE OR REPLACE TRIGGER verif_service BEFORE INSERT OR UPDATE OF numserv ON employe FOR EACH ROW
• declare
• cursor cr is SELECT numserv FROM SERVICE
• i int:=0;
• BEGIN
• for item in cr
• LOOP
• IF (item. numserv = :NEW. numserv) then i:= 1 ;
• END IF;
• END LOOP;
• if i=0 then dbms_output.put_line('N° de service n’’existe pas dans la table');
• Else dbms_output.put_line('ce service existe bien dans la table service, il peut alors être mis à jour ');
• END IF;
• END;
• /

[Link]. HPC M1 BDA USTHB 27

CREATE TRIGGER verif_service BEFORE INSERT OR UPDATE OF numserv ON employe

FOR EACH ROW

DECLARE

nbserv int;

Error exception;

BEGIN

SELECT count(numserv) INTO nbserv FROM SERVICE WHERE numserv=:[Link];

If (nbrserv = 1) then dbms_output.put_line('ce service ' || :[Link] || ' existe bien dans la table service, il peut alors être mis à jour');

Else raise Error;

End if;

Exception

When Error then raise_application_error(-20501, 'N° de service non correct');

END;

[Link]. HPC M1 BDA USTHB 28

L’année de sortie d’un film doit être <= à
l’année courante
• CREATE OR REPLACE TRIGGER ctrSortieFilm
• BEFORE INSERT OR UPDATE ON Film
• FOR EACH ROW
• DECLARE
• annéeCourante INTEGER;
• BEGIN
• Select Extract(year From (sysdate)) INTO annéeCourante FROM DUAL;
• IF annéeCourante < :[Link]ée
• THEN RAISE_APPLICATION_ERROR(-20005,‘La contrainte ctrSortieFilm n’est pas
respectée’);
• END IF;
• END;

[Link]. HPC M1 BDA USTHB 29

• Film(nomfilm, …, année)
• (Nom1, …, 2022)
• (Nom2, …, 2021)
• (Nom3, …, 2022)
• (Nom4, …, 2024)

• (nom5,…, 2024)
• ([Link],…, [Link]ée)

[Link]. HPC M1 BDA USTHB 30

• Créer un trigger nb_objet_TRIGGER qui met à jour l’attribut nb_objet
lors d'une insertion, ou d’un retrait d’un objet dans la table Objet.

• Musée (num-musée, nom, nb_objet)

• Objet (num-obj, type, num-musée*)

[Link]. HPC M1 BDA USTHB 31

• Musée (num-musée, nom, nb_objet)
• (1,….,2)
• (2,…,1)
• (3,…2)
• Objet (num-obj, type, num-musée*)
• (1,…,1)
• (2,…,3)
• (3,…,1)
• (4,…,3)
• (5,…,2)
• ([Link], [Link], [Link]-musée)
• (6,…,4)
• ([Link], [Link], [Link]-musée)

[Link]. HPC M1 BDA USTHB 32

• create or replace trigger nombreobjets after insert or update or delete on Objet
• for each ROW
• BEGIN
• CASE
• WHEN INSERTING THEN update musée set nb_objet = nb_objet + 1 where
num_musée= :new.num_musée ;
• WHEN UPDATING THEN
• update musée set nb_objet = nb_objet + 1 where num_musée= :new.num_musée ;
• update musée set nb_objet = nb_objet – 1 where num_musée= :old.num_musée ;
• WHEN DELETING THEN update musée set nb_objet = nb_objet – 1 where
num_musée= :old.num_musée ;
• END CASE;
EXCEPTION

When no_data_found THEN raise_application_error(-20502, 'N° de musée n’’existe pas dans la table');

• END;
• /

[Link]. HPC M1 BDA USTHB 33