Bases de données Actives :

Les déclencheurs (Trigger)

Présenté par:
Amal HALFAOUI (Epse GHERNAOUT)
a_halfaoui@[Link]
[Link]@[Link]
SGBD actifs (Définition)
SGBD capable de réagir à des événements afin de contrôler l’intégrité, gérer des
redondances, autoriser ou interdire des accès, alerter des utilisateurs, et plus
généralement gérer le comportement réactif des applications. (Appelé aussi réactive
ou intelligentes)

• Les mécanismes des prédicats, des procédures SQL et des déclencheurs

permettent d’incorporer dans la base de données elle-même des composants actifs qui
d’ordinaire sont inclus dans les programmes d’application.
• Elles permettent :
- de définir des contraintes d’intégrité complexes;
- de définir des comportements particuliers en cas de violation de contraintes;
- de contrôler la redondance ou de traduire des lois de comportement du domaine
d’application en comportement des données;
- d’ émettre des alertes.

2 BDD Active (Déclencheur) April 7, 2020

 Trigger (Définitions)
• Un déclencheur définit une action qui doit s’exécuter quand survient un évènement
dans la base de données (séquence Evénement-Condition-Action (ECA))
• peuvent être vus comme des programmes résidents associés à un événement particulier
(insertion, modification d’une ou de plusieurs colonnes, suppression) sur une table (ou
une vue)

. before/after E
when C
begin
A
end;

Avant/après l'événement E ...

... pour autant que la condition C soit satisfaite ...

... exécuter l'action A

3 BDD Active (Déclencheur) April 7, 2020

Trigger (Définitions)
• La majorité des déclencheurs sont programmés en PL/SQL (langage très bien adapté à
la manipulation des objets., mais il est possible d’utiliser un autre langage (C ou Java par
exemple).

• Les événements déclencheurs peuvent être :

- une instruction INSERT, UPDATE, ou DELETE sur une table (ou une vue) -
déclencheurs LMD- ;
- une instruction CREATE, ALTER, ou DROP sur un objet (table, index,
séquence, etc.) -déclencheurs LDD- ;
- le démarrage ou l’arrêt de la base (startup ou shutdown), une erreur spécifique
(NO_DATA_FOUND, DUP_VAL_ON_INDEX, etc.), une connexion ou une
déconnexion d’un utilisateur. - déclencheurs d’instances-.

• L’action associée à un déclencheur est un bloc PL/SQL enregistré dans la base. Un

déclencheur est opérationnel jusqu’à la suppression de la table à laquelle il est lié.

4 BDD Active (Déclencheur) April 7, 2020

Trigger (Définition)
• les étapes à suivre pour mettre en œuvre un déclencheur

5 BDD Active (Déclencheur) April 7, 2020

Trigger (Syntaxe et principe)
CREATE OR REPLACE trigger <nom-trigger>
<quand> <événements> ON <table>
[FOR EACH ROW [WHEN <condition>]
BEGIN
<action>
END;
- Le nom du trigger
- doit être unique dans un même schéma
- peut être le nom d'un autre objet (table, vue, procédure) mais à éviter
- quand peut être BEFORE ou AFTER.
- événements spécifie DELETE, UPDATE ou INSERT séparés par des OR.
- FOR EACH ROW est optionnel.
- Si UPDATE on peut préciser les attributs concernés (UPDATE OF listeAttributs).

6 BDD Active (Déclencheur) April 7, 2020

Trigger (Syntaxe et principe)

ORACLE propose deux types de triggers

1- les triggers lignes qui se déclenchent individuellement pour chaque ligne de
la table affectée par le trigger,

2- les triggers globaux qui sont déclenchées une seule fois.

Si l'option FOR EACH ROW est spécifiée, c'est un trigger ligne, sinon c'est un
trigger global.

7 BDD Active (Déclencheur) April 7, 2020

Trigger (Syntaxe et principe)
• Pour les triggers lignes, on peut introduire une restriction sur les lignes
à l'aide d'une expression logique SQL : c'est la clause WHEN :
- Cette expression est évaluée pour chaque ligne affectée par le trigger.
- Le trigger n'est déclenché sur une ligne que si l'expression WHEN est
vérifiée pour cette ligne.
- L'expression logique ne peut pas contenir une sous-question.
- Par exemple, WHEN ([Link]>0) empêchera l'exécution du trigger
si la nouvelle valeur de EMPNO est 0, négative ou NULL.

8 BDD Active (Déclencheur) April 7, 2020

Trigger (Syntaxe et principe)
Deux valeurs sont manipulés
- La nouvelle valeur est appelée :[Link]
- L'ancienne valeur est appelée :[Link]
Exemple : IF :[Link] < :[Link] ........

:old :new

insert null ligne insérée

delete ligne supprimée null

update ligne avant modif ligne après modif

9 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- la directive :NEW

10 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- Exemple : TrigInsQualif

11 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- Exemple : TrigInsQualif

12 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- la directive :Old
- Exemple : TrigDelQualif

13 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- la directive :Old
En considérant les données initiales des tables, le test de ce déclencheur sous SQL*Plus est le
suivant :

14 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- à la fois les directives :NEW et :OLD
Seuls les déclencheurs de type UPDATE FOR EACH ROW permettent de manipuler à la fois les
directives :NEW et :OLD.
- Exemple : TrigUpdQualif

15 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- Exemple : TrigUpdQualif

16 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- Exemple : TrigUpdQualif
le test de ce déclencheur sous SQL*Plus est le suivant :

17 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- Les prédicats conditionnels INSERTING, DELETING et UPDATING
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.

Syntaxe:
CREATE TRIGGER ...
BEFORE INSERT OR UPDATE ON nomTR
.......
BEGIN
......
IF INSERTING THEN ....... END IF;
IF UPDATING THEN ........ END IF;
......
END;

18 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
- Les prédicats conditionnels INSERTING, DELETING et UPDATING
Exemple : regroupement des deux triggers précédent en un seul

19 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Déclencheur LDD
Ce sont des déclencheurs gérant les événements liés à la modification de la structure de la
base et non plus à la modification des données de la base.

CREATE [OR REPLACE] TRIGGER [schéma.] nomDéclencheur

BEFORE | AFTER { actionStructureBase [OR actionStructureBase]… }
ON { [schéma.] SCHEMA | DATABASE } }
Bloc PL/SQL (variables BEGIN instructions END ; )
| CALL nomSousProgramme(paramètres) }

20 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Déclencheur LDD

Les principales actions sur la structure de la base prise en compte sont :

● ALTER pour déclencher en cas de modification d’un objet du dictionnaire (table, index,
séquence, etc.).
● COMMENT pour déclencher en cas d’ajout d’un commentaire.
● CREATE pour déclencher en cas d’ajout d’un objet du dictionnaire.
● DROP pour déclencher en cas de suppression d’un objet du dictionnaire.
● GRANT pour déclencher en cas d’affectation de privilège à un autre utilisateur ou rôle.
● RENAME pour déclencher en cas de changement de nom d’un objet du dictionnaire.
● REVOKE pour déclencher en cas de révocation de privilège d’un autre utilisateur ou rôle.

21 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Déclencheur LDD

Le déclencheur suivant interdit toute suppression d’objet, dans le schéma soutou, se

produisant un lundi ou un vendredi.
- Exemple

22 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principes)
• Gestion des déclencheurs
Un déclencheur est actif, comme une contrainte, dès sa création. Il est possible de le désactiver,
de le supprimer ou de le réactiver à la demande grâce aux instructions ALTER TRIGGER (pour
agir sur un déclencheur en particulier) ou ALTER TABLE (pour agir sur tous les déclencheurs
d’une table en même temps).

23 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Gestion des déclencheurs

Remarques
- Si le trigger est désactivé, le SGBD le stocke mais l’ignore.
- On peut désactiver un trigger si :
• il référence un objet non disponible
• on veut charger rapidement un volume de données important ou recharger des
données déjà contrôlées.

24 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Ordre d’exécution des déclencheurs

Si plusieurs triggers sur une même table, l’ordre d’activation est :

● tous les déclencheurs d’état BEFORE ;
● analyse de toutes les lignes affectées par l’instruction SQL;
● tous les déclencheurs de lignes BEFORE ;
● verrouillage, modification et vérification des contraintes d’intégrité ;
● tous les déclencheurs de lignes AFTER ;
● tous les déclencheurs d’état AFTER.

25 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Table Mutante
• Il est, en principe, interdit de manipuler la table sur laquelle se porte le déclencheur dans le corps
du déclencheur lui-même. Oracle parle de mutating tables (erreur : ORA-04091table ... en mutation,
déclencheur/fonction ne peut la voir).
• L’erreur n’est pas soulignée à la compilation mais est levée dès la première insertion.
• Cette restriction concerne les déclencheurs de lignes (FOR EACH ROW).
- Exemple : TrigMutant

26 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Gestion des exceptions

Si une erreur se produit pendant l'exécution d'un trigger, toutes les mises à jour produites
par le trigger ainsi que par l'instruction qui l'a déclenché sont défaites.
- On peut introduire des exceptions en provoquant des erreurs.
• Une exception est une erreur générée dans une procédure PL/SQL.
• Elle peut être prédéfinie ou définie par l'utilisateur.
• Un bloc PL/SQL peut contenir un bloc EXCEPTION gérant les différentes erreurs
possibles avec des clauses WHEN.
• Une clause WHEN OTHERS THEN ROLLBACK; gère le cas des erreurs non
prévues.

27 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Gestion des exceptions
Exceptions prédéfinies – quelques exemples
NO_DATA_FOUND
cette exception est générée quand un SELECT INTO
ne retourne pas de lignes

DUP_VAL_ON_INDEX
tentative d'insertion d'une ligne avec une valeur déjà
existante pour une colonne à index unique

ZERO_DIVIDE
division par zéro
etc

28 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
Trigger : exemple

• Exemple
employe(numemp,salaire,grade,...)
grille(grade,salmin,salmax)
/* vérifier le salaire d'un employé : */
/* s'assurer que le salaire est compris dans les bornes
correspondant au grade de l'employé */

CREATE TRIGGER verif_grade_salaire

AFTER INSERT OR UPDATE OF salaire, grade ON employe
FOR EACH ROW
DECLARE
minsal number;
maxsal number;
BEGIN
/* retrouver le salaire minimum et maximum du grade */
SELECT salmin,salmax
INTO minsal, maxsal
FROM grille

29 BDD Active (Déclencheur) April 7, 2020

 Trigger (Syntaxe et principe)
• Exemple

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'//TO_CHAR
(:[Link])//
'incorrect pour ce grade');
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20301,'Grade incorrect');
END;

30 BDD Active (Déclencheur) April 7, 2020

 Reference

 Livre SQL pour Oracle, de [Link].

31 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Syntaxe
Syntaxe:
DELIMITER $$
CREATE TRIGGER nom_trigger BEFORE INSERT
ON nom_table
FOR EACH ROW
DECLARE variable
BEGIN
[Actions à réaliser]
END $$
DELIMITER ;

• Pratiquement pareil que la syntaxe d’Oracle:

- Notion de old et new existante sans le signe ‘:’

32 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Exemple

33 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Exemple

34 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Exemple ( Base de participation au course sportive)

35 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Exemple ( Base de participation au course)
Sportif Participe

course

36 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Exemple (trigger contrôle nombre de participations)

37 BDD Active (Déclencheur) April 7, 2020

 Trigger (MySql)
• Différences avec Oracle
- Déclencheur ligne seulement: ne supporte pas les déclencheurs
globaux (statement)
- Pas de clause When: pas de restriction possible sur les lignes
- Pas de update of colonne(s): pas de possibilité d’utiliser un
déclencheur à la mise à jour d'une ou plusieurs colonnes,
- Ne supporte pas plusieurs trigger par table pour la même
opération (varsions avant 5.7) : finalement on aura un max de 6
triggers par table (possible dans la dernière version 5.7)
- Ne permet pas de grouper les traitements d’opérations
différentes dans un seul trigger (insert or delete or update )
- Attention à l’option de foreign key (delete on cascade) les suppressions
générées par les tables liées ne déclenchent pas le trigger !!

38 BDD Active (Déclencheur) April 7, 2020