TRIGGERS

EN
PL/SQL

Préparé par : Larbi HASSOUNI

 1,1 Subal terne
EstSuperi eurDe
Employe
NumEmploye
0,n Superi eur
Nom
Prenom
Foncti on
EstRattache
DateEmbauche Departement
1,1
Sal ai re 1,n NumDept
Compl ementSal ai re NomDept
NatureCompl ementSal ai re
Gerer >
DateNai ssance 0,n
Genre
Si tFam 1,1
NbEnfants
T el ephone
Emai l Commande
NumCommande 1,n
DateCommande Contenir >
Client MontantRegl e QteCommandee
CodeClient EtatCommande Pri xVente
NomCl i ent DateLi vrai son T auxRemi se
Adresse
Vi l l e 1,1 0,n
CodePostal 1,n
Pays Passer >
T el ephone
Emai l
Si teWeb
Produit
Reference
Desi gnati on
Depot 0,n Stocker 1,n Pri xAchat
CodeDepot MargeMi n
QteEnStock
AdresseDepot MargeMax
Seui l Stock
Vi l l eDepot

1,1 1,1
Fournisseur
CodeFournisseur 1,n
Rai sonSoci al e Fournir >
Contact
Adresse
Vi l l e
CodePostal
Pays Categorie
1,n
T el ephone CodeCategorie Appartenir
Emai l Li bel l eCategori e
Si teWeb Descri pti on
Il l ustrati on
2
Hassouni Larbi PL/SQL : Eléménts de base du langage 3
Hassouni Larbi PL/SQL : Eléménts de base du langage 4
 Vue générale sur les Triggers
Un trigger est un bloc PL / SQL nommé stocké dans la base de données
Oracle et exécuté automatiquement lorsqu'un événement déclencheur
se produit. L'événement peut être l'un des suivants :
• Exécution d'une instruction de langage de manipulation de données
(LMD) sur une table, par exemple, INSERT, UPDATE ou DELETE. Par
exemple, il est possible de définir un trigger qui s'exécute
automatiquement lorsqu'on insère une nouvelle ligne dans la table
Employé. L'exécution peut être faite avant ou après l'insertion de la
ligne selon ce qui a été spécifié dans la définition du trigger.
• Exécution d'une instruction LDD (Langage de Définition des
Données), par exemple, une instruction CREATE, ALTER ou DROP.
Ces déclencheurs sont souvent utilisés à des fins d’audit pour
enregistrer les modifications du schéma.
• Un événement système tel que le démarrage ou l’arrêt de la base
de données Oracle.
• Un événement utilisateur tel que connexion ou déconnexion.
Le fait d’exécuter un trigger est également appelé déclenchement (to
fire) d’un trigger. Nous disons que le trigger est déclenchée.

Larbi HASSOUNI Triggers 5

 Vue générale sur les Triggers
• Contrairement à une procédure stockée, vous pouvez activer
ou désactiver un trigger, mais vous ne pouvez pas l'invoquer
de façon explicite.
• Lorsqu'un trigger est activé (enabled), la base de données
l'invoque automatiquent, on dit que le trigger se déclenche
(fires), chaque fois que son évenement déclencheur se
produit.
• Lorsqu'un trigger est désactivé (disabled), il ne se déclenche
pas (does not fire) même si son événement déclencheur se
produit.
• ALTER TRIGGER <nom_trigger> {ENABLE|DISABLE}
 Utilités des triggers

Les triggers Oracle sont utiles dans de nombreux

cas, parmi lesquels nous pouvons citer :
• Appliquer des règles de gestion complexes qui ne
peuvent pas être établies à l'aide de contraintes
d'intégrité telles que UNIQUE, NOT NULL et
CHECK.
• Eviter ou empêcher les transactions non valides..
• Collecter des informations statistiques sur les
accès aux tables.
• Générer automatiquement une valeur pour les
colonnes dérivées (ou virtuelles).
• Auditer les données sensibles.

Larbi HASSOUNI Triggers 7

 Conception d'un trigger
• Vous créez un trigger à l'aide de l'instruction CREATE TRIGGER.
Dans laquelle vous spécifiez:
– l'événement de déclenchement en indiquant l'instruction de
déclenchement et l'entité sur laquelle cette dernière opère.
– On dit que le trigger est créé ou défini sur l'entité qui peut être:
• Une table
• Une vue
• Un schéma,
• la base de donnée
– le moment de déclenchement du trigger
– Le nombre de fois que le corps du trigger est exécuté.
• Pour chaque ligne affectée par l'instruction de déclenchement
(trigger ligne)
• Une seule fois lorsque l'instruction de déclenchement est exécutée
(trigger instruction).
 Conception des Triggers
Pour concevoir des triggers, tâcher de suivre les
recommandations suivantes:
❑Utiliser les triggers pour garantir que lorsqu’une
opération spécifique est effectuée, des actions
reliées sont éffectuées.
❑Ne pas définir des triggers qui duppliquent des
fonctions (ou caractéristiques) déja construites
dans la base de données.
Par exemple, ne définissez pas des triggers qui rejettent
les mauvaises données si vous pouvez faire la même
chose à l’aide des contraintes d’intégrité.

Larbi HASSOUNI Triggers 9

 Conception des Triggers
■ Limiter la taille des triggers. Si la logique de votre
trigger requiert plus de 60 lignes de codes en PL/SQL, il
vaut mieux inclure la plus gande partie du code dans une
procédure, puis appeler la procédure à partir du trigger.

■ Utiliser les triggers uniquement pour les opérations

globales et centralisées, qui seront déclenchés par des
instructions indépendamment de l’utilisateur ou de
l’application qui a lancé ces instructions.

Larbi HASSOUNI Triggers 10

■ Ne créez pas des triggers récursifs. Par exemple, créer
un trigger “AFTER UPDATE” sur la table Emp_tab, qui à
son tour contient une instrction UPDATE sur la même
table cause le déclencenchement récursif du trigger.

■ Utiliser judicieusement les triggers sur la base de

données (on DATABASE). Ils sont exécutés pour chaque
utilisateur, et chaque fois que l’événement, sur lequel
le trigger est créé, se produit.

Larbi HASSOUNI Triggers 11

 Création d'un trigger
La syntaxe pour créer un trigger LMD est :
CREATE [OR REPLACE ] TRIGGER <nom-trigger>
{BEFORE |AFTER |INSTEAD OF}
<événement_déclenchant>
[clause referencing]
[WHEN <condition_déclenchement>]
[FOR EACH ROW]
DECLARE
Partie_déclaration
BEGIN
Instructions_executables
EXCEPTION
Instructions de gestion des exceptions
END;
/
Larbi HASSOUNI Triggers 12
 Céation d'un trigger
La syntaxe pour créer un trigger systeme est :

CREATE [OR REPLACE] TRIGGER [<shéma>.]<nom_déclencheur>

{BEFORE | AFTER}
{<liste_événement_LDD>|<liste événements_BD>}
ON {DATABASE|[<schéma>.]SHEMA}
[<clause WHEN>]
<corp_déclencheur>

Larbi HASSOUNI Triggers 13

 Création des Triggers

Les triggers sont crées à l’aide de l’instruction : CREATE TRIGGER.

Cette instruction peut être utilisé avec un n’importe quel outil

intéractif, tel que

• SQL Developer,

• SQL*Plus

• ou Enterprise Manager.

Larbi HASSOUNI Triggers 14

Comme vous pouvez le constater, le corps du trigger a la
même structure qu'un bloc anonyme PL / SQL.
1) CREATE OR REPLACE
Crée un nouveau trigger ou modifie un trigger existant.
2) Nom_Trigger
Spécifie le nom du nouveau trigger à créer ou celui d'un
trigger vexistant à modifier.
3) BEFORE | AFTER
L'option BEFORE ou AFTER indique le moment où le
déclencheur est déclenché, avant ou après un
événement déclencheur, par exemple, INSERT,
UPDATE ou DELETE.

Larbi HASSOUNI Triggers 15

 4) ON nom_table
Nom_table est le nom de la table associée au trigger.
5) FOR EACH ROW
La clause FOR EACH ROW spécifie que le trigger est du niveau
Enregistrement ou ligne. Un trigger de niveau ligne se déclenche
une fois pour chaque ligne insérée, mise à jour ou supprimée.
Outre les triggers de niveau ligne, nous avons les triggers de
niveau instruction. Un trigger de niveau instruction se déclenche
une fois, quel que soit le nombre de lignes affectées par
l'événement déclencheur. Si vous omettez la clause FOR EACH
ROW, l'instruction CREATE TRIGGER créera un déclencheur de
niveau instruction.

Larbi HASSOUNI Triggers 16

Exemple de création d'un trigger

Supposons que nous voulions enregistrer des actions sur la table Client
chaque fois qu'un client est mis à jour ou supprimé. Pour faire ceci:
Commençons d'abord par créer une nouvelle table pour enregistrer les
événements UPDATE et DELETE:

CREATE TABLE Audits (

NomTable VARCHAR2(255),
NomTransaction VARCHAR2(10),
Utilisateur VARCHAR2(30),
DateTransaction DATE
);

Larbi HASSOUNI Triggers 17

 Deuxièmement, créons un nouveau trigger associé à la table "Client":
CREATE OR REPLACE TRIGGER Client_Audit_trigger
AFTER UPDATE OR DELETE
ON Client
FOR EACH ROW
DECLARE
l_transaction VARCHAR2(10);
BEGIN
-- determine le type de la transaction
l_transaction := CASE
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END;

-- inserer une ligne dans la table Audit

INSERT INTO Audits (NomTable, NomTransaction, Utilisateur,
DateTransaction)
VALUES('Client', l_transaction, USER, SYSDATE);
END;
/
Larbi HASSOUNI Triggers 18
L'instruction suivante:

UPDATE Client
SET Ville = 'Rabat'
WHERE CodeClient = 'CLT002';
déclenche le trigger après la mise à jour d’une ligne de la table Client.
À l'intérieur du déclencheur, nous déterminons l'action en cours, qu'il s'agisse
de UPDATE ou DELETE, et insérons une ligne dans la table des audits.
Maintenant, vérifiez le contenu de la table Audits pour voir si le trigger a été
activé:

SELECT *
FROM Audits;

Comme vous pouvez le voir clairement dans la sortie, le trigger a été

déclenché, de sorte qu'une nouvelle ligne est insérée dans la table Audits.

Larbi HASSOUNI Triggers 19

Supprimons maintenant une ligne de la table Client en exécutant l'instruction ci-
dessous:
DELETE
FROM Client
WHERE CodeClient = 'CLT011';

Vérifions le contenu de la table Audits pour voir si le trigger a été activé:

SELECT *
FROM Audits;

La sortie a montré qu'une nouvelle ligne a été insérée. Cela signifie que
l'action DELETE a déclenché le trigger.

Larbi HASSOUNI Triggers 20

Trigger de niveau instruction (Statement-level Trigger)
• Un déclencheur de niveau instruction est activé lorsqu'un événement
déclencheur se produit sur une table, quel que soit le nombre de lignes
affectées. En d'autres termes, un déclencheur de niveau instruction
s'exécute une fois pour chaque transaction.
• Par exemple, si vous mettez à jour 1 000 lignes dans une table, un
déclencheur de niveau instruction sur cette table ne sera exécuté qu'une
seule fois.
• En raison de ses fonctionnalités, un déclencheur au niveau instruction
n'est pas souvent utilisé pour des activités liées aux données telles que
l'audit des modifications des données dans la table associée. Il est
généralement utilisé pour appliquer des mesures de sécurité
supplémentaires sur le type de transaction pouvant être effectuée sur une
table.
• Par défaut, l'instruction CREATE TRIGGER crée un trigger de niveau
instruction lorsque vous omettez la clause FOR EACH ROW.

Larbi HASSOUNI Triggers 21

Voici la syntaxe de base pour créer un trigger de niveau instruction :

CREATE OR REPLACE TRIGGER Client_Audit_trigger

AFTER UPDATE OR DELETE
ON Client

DECLARE
l_transaction VARCHAR2(10);
BEGIN
-- determine le type de la transaction
l_transaction := CASE
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END;

-- inserer une ligne dans la table Audit

INSERT INTO Audits (NomTable, NomTransaction, Utilisateur, DateTransaction)
VALUES('Client', l_transaction, USER, SYSDATE);
END;
/

Larbi HASSOUNI Triggers 22

 Exemple1 de trigger du niveau instruction
• Supposons que vous souhaitiez empêcher les utilisateurs de mettre à jour
le prix d'achat des produits du 28 au 31 de chaque mois afin de pouvoir
caclculer la valeur du stock dans cette date de chaque mois.
• Pour appliquer cette règle, vous pouvez utiliser ce déclencheur de niveau
instruction :

CREATE OR REPLACE TRIGGER Produit_PrixAchat_Trigger

BEFORE UPDATE OF PrixAchat
ON Produit
DECLARE
l_JourDuMois NUMBER;
BEGIN
--Determine le jour de la transaction
l_JourDuMois := EXTRACT(DAY FROM sysdate);
IF l_JourDuMois BETWEEN 28 AND 31 THEN
raise_application_error(-20100,'Ne peut pas modifier le PrixAchat entre 28
et 31 du mois.');
END IF;
END;
/
Larbi HASSOUNI Triggers 23
 Testons le trigger de niveau instruction en exécutant l'instruction
ci-dessous:
UPDATE Produit
SET PrixAchat = PrixAchat *1.01;

Oracle engendre l'erreur ci-dessous:

Notez que Oracle annule automatiquement la mise à jour car nous appelons
la procédure raise_application_error dans le déclencheur.

Larbi HASSOUNI Triggers 24

 Trigger de niveau ligne (Row-level Triggers)
Les déclencheurs de niveau ligne se déclenchent une fois pour
chaque ligne affectée par l'événement déclencheur, telle que
INSERT, UPDATE ou DELETE.
Les déclencheurs au niveau de la ligne sont utiles pour les
activités liées aux données telles que l’audit et la validation
des données.
Pour créer un nouveau déclencheur au niveau de la ligne,
vous utilisez l'instruction CREATE TRIGGER avec la clause FOR
EACH ROW.
Les fonctionnalités applicables aux déclencheurs de niveau
instruction sont également disponibles comme déclencheur
de niveau ligne:
▪ Utilisez raise_application_error () pour déclencher une
exception définie par l'utilisateur
▪ Vérifiez si l'événement en cours est INSERT, UPDATE ou DELETE
en utilisant INSERTING, DELETING ou UPDATING.

Larbi HASSOUNI Triggers 25

De plus, les déclencheurs au niveau des lignes vous permettent de
suivre les valeurs BEFORE et AFTER. En utilisant les colonnes des
variables de liaisons : OLD et : NEW
Puisque les triggers de niveau ligne s'exécutent dans le contexte d'une
seule ligne, vous pouvez accéder aux anciennes et aux nouvelles valeurs
de colonne à l'aide de la syntaxe suivante:
▪ :OLD.NomColonne
▪ :NEW.NomColonne
Par exemple, cette instruction vérifie la valeur de la colonne Salaire
pour voir si le nouveau Salaire est supérieur au Salaire actuel:
1 IF :NEW.Salaire > :OLD.Salaire THEN
2 -- instrcutinons
3 END IF;

Larbi HASSOUNI Triggers 26

 Identifiants de corrélation
dans les déclencheurs de niveau ligne
• Un déclencheur de niveau ligne est exécuté une fois pour
chaque ligne traitée par l'instruction déclenchante.
• A l'intérieur du corps du trigger, il est possible d'accéder aux
données de la ligne en cours de traitement au moyen de deux
identifiants de corrélation :old et :new
• Un identifiant de corrélation est un type spécial de variable
PL/SQL de liaison que le compilateur traite comme des
enregistrements du type: table_déclenchante%ROWTYPE
où table_déclenchante est la table sur laquelle est défini le
trigger.

Larbi HASSOUNI Triggers 27

• Ainsi une réference telle que :
:new.champ
Ne sera valide que si champ est un champ de la table
déclenchante.
La signification de :old et :new dépend de l'instruction
déclenchante comme le montre le tableau ci-après.
:old est non défini pour les instructions INSERT, et : new est
non défini pour les instructions DELETE.
Le compilateur PL/SQL ne générera pas d'erreur si vous
utilisez :old dans un INSERT ou :new dans un DELETE, mais
les valeurs de champ de chacun d'eux seront NULL.

Larbi HASSOUNI Triggers 28

 Instruction
:old :new
déclenchante
INSERT Non défini- tous les Valeurs qui seront insérées
champs sont NULL au terme de l'instruction
UPDATE Valeurs d'origine Nouvelles valeurs qui
de la ligne avant remplaceront celles
mise à jour d'origine au terme de
l'instruction

DELETE Valeurs d'origine Non défini- tous les

avant que la ligne champs sont NULL
ne soit supprimée

Larbi HASSOUNI Triggers 29

Remarques:

:old et :new ne sont pas de véritables enregistrements car ils ne se

prêtent pas aux opérations habituellement valides sur les
enregistrement.
Par exemple, ils ne permettent l'assignation de valeurs qu'à des
champs individuellement, et non à un enregistrement dans sa
globalité.
On dit que ce sont des pseudos enregistrements.

Les enregistrements :old et :new sont valides uniquement dans des

triggers de niveau ligne;
Une erreur de compilation résulterait de toute tentative de se
référer à l'un ou à l'autre dans un trigger de niveau instruction
puisque ce dernier ne s'exécute qu'une seule fois, quel que soit le
nombre de lignes traitées par l'instruction.
Larbi HASSOUNI Triggers 30
il existe un identifiant de corrélation additionnel, :parent.
Lorsque le trigger est défini sur une table imbriquée, :old et :new
se réfèrent aux lignes de la table imbriquée tandis que :parent se
réfère à la ligne courante de la table parent. (Voir table imbiquée)

Larbi HASSOUNI Triggers 31

• Considération de performance
Un trigger de niveau ligne se déclenche chaque
fois qu'une ligne est affectée par un événement
déclencheur. Par exemple, si vous mettez à jour
1 000 lignes dans une table, le trigger se
déclenchera 1000 fois, ce qui peut
éventuellement entraîner un problème de
performances.

Larbi HASSOUNI Triggers 32

Pour spécifier une condition de déclenchement du trigger, vous pouvez
utiliser la clause WHEN. Par exemple, le trigger suivant ne se déclenche que
lorsque vous mettez à jour la MargeMin d'un produit avec une valeur qui
reste inférieure ou égale MargeMax :

CREATE OR REPLACE TRIGGER

BEFORE UPDATE OF MargeMin
ON Produit
FOR EACH ROW
WHEN NEW.MargeMin <= OLD.MargeMax
......
Dans certaines situations, l'utilisation de la condition dans WHEN peut améliorer
considérablement les performances de la base de données.
Notez que vous pouvez utiliser OLD et NEW dans la clause WHEN. De plus, vous
n’utilisez pas de signe deux-points (:) comme préfixe pour ces variables.

Larbi HASSOUNI Triggers 33

Exemple1 de trigger de niveau ligne
• Nous allons utiliser la table Employé pour présenter un exemple de Trigger
de niveau-ligne avec la condition WHEN.
• Le trigger ci-dessous s'exécute chaque fois qu'on modifie le complément
du salaire d'un employé.
CREATE OR REPLACE TRIGGER Employe_CompSal_Trig
BEFORE UPDATE OF ComplementSalaire
ON Employe
FOR EACH ROW
WHEN (NEW.ComplementSalaire <= OLD.Salaire)
BEGIN
IF :NEW.ComplementSalaire >= 2*:OLD.ComplementSalaire THEN
raise_application_error(-20101,'Le complément du salaire' ||
:NEW.ComplementSalaire ||
' ne peut pas augmenter du simple au double: ' ||
:OLD.ComplementSalaire);
END IF;
END;
/
Larbi HASSOUNI Triggers 34
• Tester le trigger
• Commençons par afficher les informations NumEmploye, Salaire et
ComplementSalaire sur les employés du Dept 20.
SELECT NumEmploye, Salaire, ComplementSalaire
FROM Employe
WHERE NumDept = 20;

Larbi HASSOUNI Triggers 35

L'exécution de l'instruction UPDATE ci-dessous déclenche le trigger
UPDATE Employe
SET ComplementSalaire = ComplementSalaire + 400
WHERE NumDept = 20;
Mais comme le complement de salaire de l'employé dont le numéro est
25012 passe de 300 à 700 la condition
:NEW.ComplementSalaire >= 2*:OLD.ComplementSalaire
et l'instruction ci-dessous :
raise_application_error(-20101,'Le complément du salaire' || :NEW.ComplementSalaire
|| ' ne peut pas augmenter du simple au double: ' || :OLD.ComplementSalaire);

Qui est envoyé par Oracle.

Remarquez qu'il y a un ROLLBACK automatique qui est effectué à cause de
l'erreur produite à l'intérieur du trigger.

Larbi HASSOUNI Triggers 36

Exemple2 de création d'un trigger de niveau ligne

CREATE OR REPLACE TRIGGER AfficheChangmentSalaires

BEFORE DELETE OR INSERT OR UPDATE ON Employe
FOR EACH ROW
WHEN (NEW.NumEmploye > 0)
DECLARE
l_sal_diff NUMBER;
BEGIN
l_sal_diff := :NEW.salaire - :OLD.salaire;
DBMS_OUTPUT.PUT('Old salaire: ' || :OLD.salaire);
DBMS_OUTPUT.PUT(' New salaire: ' || :NEW.salaire);
DBMS_OUTPUT.PUT_LINE(' Difference ' || l_sal_diff);
END;
/
Larbi HASSOUNI Triggers 37
Ce trigger est déclenché lorsqu’une instruction LMD (INSERT,
UPDATE, DELETE) est exécutée sur la table Employe.
Vous pouvez choisir la combinaison d’instructions qui vont
déclencher le trigger.
Puisque le trigger utilise le mot clé BEFORE, il peut accéder aux
nouvelles valeurs avant qu’elles ne soient stockées dans la table,
et il peut les modifier dans le cas où il ya une légère erreur à
corriger en modifiant :NEW.NomColonne.
Puisque le trigger utilise la clause FOR EACH ROW, il peut être
exécuté plusieurs fois, lorsqu’on modifie ou supprime plusieurs
lignes.
Vous devez omettre cette clause dans le cas ou vous voulez
simplement enregistrer le fait qu’il ya exécution d’une instruction
LMD sur la table, sans examiner les données pour chaque ligne.
Larbi HASSOUNI Triggers 38
Une fois le trigger est créé, l’exécution de l’instruction UPDATE ci-
dessous, déclenche le trigger une fois pour chaque ligne qui a été
mise à jour, et imprimera donc pour chaque ligne le nouveau
salaire, l’ancien salaire, et la différence.
Note: La taille d’un trigger ne peut pas dépasser 32K.
UPDATE Employe
SET salaire = salaire + 500.00
WHERE NumDept = 10;

Larbi HASSOUNI Triggers 39

 Types des Triggers
Oracle exécute automatiquement un trigger lorsqu’un
évément spécifique se produit.
L’événement déclenchant peut être :
• Une opération LMD (INSERT, UPDATE, DELETE)
portant sur une table ou sur certains types de
vue;
• un événement système tel que le démarrage ou
la fermeture d'une instance de base de
données;
• Ou certains types d'opérations LDD.

Larbi HASSOUNI Triggers 40

Les triggers peuvent donc être de trois types différents :
❑triggers LMD sur les tables.
❑triggers INSTEAD OF sur les vues.
❑triggers systèmes sur la base de données (DATABASE)
ou un schèma (SCHEMA)
❖Les triggers sur la DATABASE, se déclenchent pour
chaque événement pour tous les utilisateurs.
❖Les triggers sur un SCHEMA, se déclenchent pour
chaque événement pour l’utilisateur spécifié.

Larbi HASSOUNI Triggers 41

 Evénements Systèmes

Vous pouvez créer des triggers qui se déclenchent à

chaque événement produit par une des opérations
suivantes:
❑Instructions LMD (DELETE, INSERT, UPDATE)
❑Instructions LDD (CREATE, ALTER, DROP)
❑Opérations sur la Database (SERVERERROR,
LOGON, LOGOFF, STARTUP, SHUTDOWN)

Larbi HASSOUNI Triggers 42

 Nomination des Triggers
❑Les noms des triggers doivent être uniques
relativement aux autres triggers du même schéma.

❑Un trigger peut avoir le même nom qu’un autre

objet, qui n’est pas un trigger, telle qu’une table, une
vue, ou une procédure, du même schéma.

❖Par exemple, une table et un trigger peuvent

avoir le même nom, mais pour éviter la confusion,
il est recommandé de donner des noms différents
Larbi HASSOUNI Triggers 43
 Quand est-ce-que le trigger est déclenché ?

Le déclenchement d’un trigger est basé sur une

instruction de déclenchement qui spécifie :
❑L’instruction SQL ou l’événement système,
événement DATABASE, ou événement DDL, qui
déclenche le corps du trigger.
❑Parmi les options DELETE, INSERT, et UPDATE, il est
possible d’inclure une, deux ou les trois options dans
l’instruction de déclenchement.
❑la table, vue, DATABASE, ou SCHEMA associée avec le
trigger.

Larbi HASSOUNI Triggers 44

Par exemple, le trigger AfficheChangementSalaires se
déclenche après le lancement de l’exécution de
n’importe quelle instruction DELETE, INSERT, ou
UPDATE sur la table Employe.
Les instructions ci-dessous sont des exemples
d’instructions qui déclenchent le trigger
AfficheChangementSalaires de l’exemple précédent:
❑DELETE FROM Employe;
❑INSERT INTO Employe VALUES ( ... );
❑INSERT INTO Employe SELECT ... FROM ... ;
❑UPDATE Employe SET ... ;
Larbi HASSOUNI Triggers 45
 Remarques importantes
On ne peut spécifier qu’une seule table ou vue dans
une instruction de déclenchement.

Si l’option INSTEAD OF est utilisée, l’instruction de

déclenchement doit spécifier une vue.

Inversement, si une vue est spécifiée dans une

instruction de déclenchement, seule l’option INSTEAD
OF est autorisée.

Larbi HASSOUNI Triggers 46

 Comment la liste des colonnes affecte
les triggers UPDATE?
Une instruction UPDATE peut inclure une liste de
colonnes.
Si une instruction de déclenchement UPDATE inclue
une liste de colonnes, le trigger est déclenché
uniquement lorsqu’une des colonnes spécifiées est
modifiée.
Si une instruction de déclenchement UPDATE n’inclue
pas de liste de colonnes, le trigger est déclenché
lorsqu’une colonne quelconque de la table associée
est modifiée.
Larbi HASSOUNI Triggers 47
 On ne peut pas spécifier une liste de colonnes avec les
instructions de déclenchement INSERT ou DELETE.

Le trigger AfficheChangementSalaires peut inclure

une liste de colonne dans l’instruction de
déclenchement :

... BEFORE DELETE OR INSERT OR UPDATE OF Salaire

ON Employe ...

Larbi HASSOUNI Triggers 48

 Contrôler le temps de déclenchement du trigger
Options BEFORE et AFTER

L’option BEFORE ou AFTER dans l’instruction de

déclenchement spécifie exactement quand le corps du
trigger est exécuté en relation avec l’exécution de
l’instruction de déclenchement.
Dans l’instruction CREATE TRIGGER , l’option BEFORE
ou AFTER est spécifiée juste avant l’instruction de
déclenchement.
Par exemple, le trigger AfficheChangementSalaires de
l’exemple précédent est un trigger BEFORE.

Larbi HASSOUNI Triggers 49

 Contrôler le temps de déclenchement du trigger
Options BEFORE et AFTER

En général, vous utiliserez les triggers BEFORE ou

AFTER pour effectuer les opérations suivantes:

❑Utiliser les triggers BEFORE pour modifier une

ligne avant qu’elle ne soit écrite sur le disque.

❑Utiliser les triggers AFTER pour obtenir, et

effectuer des opérations, en utilisant le ROWID.

Larbi HASSOUNI Triggers 50

 Déclencher les Triggers une ou plusieurs fois
(Option FOR EACH ROW )
L’option FOR EACH ROW détermine si le trigger est un
“trigger ligne” ou un “trigger instruction”.
Si on spécifie FOR EACH ROW, le trigger sera déclenché
une fois pour chaque ligne affectée par l’instruction de
déclenchement.
L’absence de l’option FOR EACH ROW indique que le
trigger sera déclenché une seule fois à chaque
exécution de l’instruction de déclenchement, mais pas
séparément pour chaque ligne affectée par
l’instruction.
Larbi HASSOUNI Triggers 51
 Trigger lignes
Exemple de trigger :
CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Sal > 1000)
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary,
Action)
VALUES (:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');
END;
Larbi HASSOUNI Triggers 52
L’istruction suivante:

UPDATE Emp_tab SET Sal = Sal + 1000.0

WHERE Deptno = 20;

Déclenchera le trigger pour chaque ligne affectée,

c’est_àdire pour chaque employé du département 20.
Ainsi s’il ya 5 employés dans le département 20, le
trigger sera déclenché 5 fois.

Larbi HASSOUNI Triggers 53

 Trigger instruction
Le trigger ci-dessous sera déclenché une seule fois à chaque
exécution de l’instruction UPDATE sur la table Emp_tab:

CREATE OR REPLACE TRIGGER Log_emp_update

AFTER UPDATE ON Emp_tab
BEGIN
INSERT INTO Emp_log (Log_date, Action)
VALUES (SYSDATE, 'Emp_tab COMMISSIONS CHANGED');
END;
Les triggers de niveau instruction sont utiles pour effectuer des
contrôles de validation pour toute l’instruction.
Larbi HASSOUNI Triggers 54
 Conditions pour le Déclenchement des triggers
(WHEN Clause)
Il est possible de conditionner le déclenchement d’un
trigger ligne en spécifiant dans sa définition, une
expression SQL booléene dans une clause WHEN.
La condition inclue par la clause WHEN est évaluée pour
chaque ligne afféctée par le trigger.
Si l’évaluation de l’expression produit un résultat TRUE
pour une ligne, alors le corps du trigger est exécutée
pour cette ligne. Cependant, si le résultat est FALSE ou
NULL, le corps du trigger n’est pas exécuté pour la ligne
en question.
Larbi HASSOUNI Triggers 55
L’évaluation de la clause WHEN n’a aucun effet sur
l’instruction de déclenchement du trigger
(autrement dit, il n’y a pas de ROLLBACK de l’instruction
de déclenchement du trigger, si la clause WHEN s’évalue
en FALSE).

Par exemple, dans le trigger AfficheChangementSalaires,

son corps n’est pas exécuté si la nouvelle valeur de
Empno est zero, NULL, ou négative.

Larbi HASSOUNI Triggers 56

 Triggers de Schéma (Shema Triggers)
• Un trigger de schéma est un trigger déclenché sur un
événement particulier de n'importe quel objet du
schéma. Il se déclenche lorsque l'utilisateur
propriétaire du schéma est l'utilisateur actuel et
initie l'événement déclencheur.
• Les triggers de schéma peuvent être créés sur des
événements DDL ou sur des événements de base de
données. Le tableau ci-dessous répertorie les
différents événements.

Larbi HASSOUNI Triggers 57

Pour créer un trigger de schéma on peut utiliser la syntaxe ci-dessous :

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER} trigger_event [OR event...]
ON SCHEMA
[DECLARE
variables]
BEGIN
plsql_code
[EXCEPTION exception_code]
END;

Larbi HASSOUNI Triggers 58

 La principale différence entre la syntaxe de ces triggers et celle
des triggers DML réside dans le mot clé «ON SCHEMA» et les
événements de déclenchements réellement utilisés.
Les exemples des différents événements sont très similaires,
L'exemple ci-dessous donne un exemple de trigger de schéma.

CREATE OR REPLACE TRIGGER grant_trigger

AFTER GRANT
ON SCHEMA
DECLARE
grant_list dbms_standard.ora_name_list_t;
BEGIN
INSERT INTO grant_audit (current_user, grant_date, grantee)
VALUES ( USER, SYSDATE, grant_list(1) );
END:
/
Larbi HASSOUNI Triggers 59
Si vous souhaitez créer un trigger qui se déclenche sur plusieurs événements,
vous pouvez utiliser le mot clé OR entre chaque événement.
Voici un exemple de trigger qui se déclenche lors d'un événement GRANT ou
REVOKE.
CREATE OR REPLACE TRIGGER grant_trigger
AFTER GRANT OR REVOKE
ON SCHEMA
DECLARE
grant_list dbms_standard.ora_name_list_t;
BEGIN
INSERT INTO grant_audit (current_user, grant_date, grantee)
VALUES ( USER, SYSDATE, grant_list(1) );
END;
/
Il existe toute une gamme de commandes et de fonctions que vous pouvez
utiliser lorsque vous travaillez avec des événements système (voir annexe)

Larbi HASSOUNI Triggers 60

 Triggers de Bases de Données
(Database Triggers)
• Un trigger de base de données est créé sur la base de
données et est exécuté chaque fois qu'un événement
spécifique à la base de données se produit. Cela se produit
quel que soit l'utilisateur connecté à la base de données.
• Vous pouvez créer des triggers de base de données sur les
événements DDL mentionnés dans la section DDL et
événements de base de données. Cela signifie que le trigger
se déclenchera pour tous les utilisateurs qui effectuent
l'événement, pas seulement si l'utilisateur actuel est
propriétaire du schéma.
• Le tableau ci-après présente les différents événements
valides.

Larbi HASSOUNI Triggers 61

Voici un exemple de trigger de base de données :

CREATE OR REPLACE TRIGGER trg_logon_audit

AFTER LOGON
ON DATABASE

BEGIN
INSERT INTO logon_audit (current_username, current_date)
VALUES (USER, SYSDATE);
END;
/

Larbi HASSOUNI Triggers 62

 Evénements LDD et de base de données
(DDL and Database Events)
Les déclencheurs de schéma et de base de données
peuvent être déclenchés sur des événements DDL et de
base de données.
Un événement DDL se produit lorsqu'une instruction
DDL est exécutée et un événement de base de données
lorsque des événements spécifiques se produisent dans
la base de données.

Larbi HASSOUNI Triggers 63

 Triggers LDD

• Trigger pour consigner une activité LDD sur un schéma.

CREATE OR REPLACE TRIGGER <nom_trigger>
<BEFORE | AFTER> <action_déclenchement>
ON <SCHEMA | DATABASE>

DECLARE
-- Déclarations de variables
BEGIN
-- Code dut trigger
EXCEPTION
-- Gestionnaire des exceptions
END <nom_trigger>;
/

Larbi HASSOUNI Triggers 64

 Trigger To Log Multiple DDL Activities
CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action> OR <trigger_action>
ON SCHEMA

DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
-- exception handlers
END <trigger_name>;
/

Larbi HASSOUNI Triggers 65

 Table pour capturer la sortie d'un trigger DDL
CREATE TABLE ddl_log (
operation VARCHAR2(30),
object_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE
);

Larbi HASSOUNI Triggers 66

 Trigger pour enregistrer les informations sur les
opérations CREATE effectué sur la base
CREATE OR REPLACE TRIGGER BeforeCreate_trigger
BEFORE CREATE
ON SCHEMA

DECLARE
oper ddl_log.operation%TYPE;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, NULL, USER, SYSDATE
FROM DUAL;
END BeforeCreate_trigger;
/

Larbi HASSOUNI Triggers 67