Langage PL/SQL
Chapitre 6 : Triggers
Dr Konan Marcellin BROU
[email protected]
2O23-2024
Sommaire
Introduction
Trigger par ordre
Trigger ligne
Trigger avec l’option AFTER
Gestion des triggers
Bibliographie
K. M. BROU Langage PL/SQL : Triggers 2
1
I. Introduction
Objectifs :
Comprendre le concept de de
trigger
Savoir déclarer et utiliser un
trigger
Assurer la gestion des triggers
K. M. BROU Langage PL/SQL : Triggers 3
I. Introduction
1.1. Présentation
Définition d’un trigger ou
déclencheur
Procédure stockée qui s’exécute
automatiquement quand un
événement se produit.
Evénement
Actions de mise à jour sur une
table avant ou après un ordre :
INSERT ou UPDATE ou DELETE.
Intérêts
Vérification de contraintes
d’intégrité.
Auditer des actions sur une table
Calculer des valeurs dérivées pour
d'autres colonnes de la table
K. M. BROU Langage PL/SQL : Triggers 4
2
I. Introduction
1.2. Caractéristiques d’un Deux sortes de triggers :
trigger Trigger par ordre ou trigger
global
Associé à une et une seule table.
Traitement exécuté une fois
Opérationnel jusqu’à la pour la table.
suppression du trigger ou de la Trigger ligne :
table. Traitement exécuté pour
Actif ou inactif. chaque ligne de la table
Création d’un curseur a chaque concernée par l’événement.
exécution d’un trigger.
K. M. BROU Langage PL/SQL : Triggers 5
I. Introduction
1.3. Typologie des triggers Douze combinasons de triggers :
Traitement avant : BEFORE
Traitement après : AFTER Type BEFORE Type AFTER
BEFORE UPDATE ligne AFTER UPDATE ligne
Trigger BEFORE DELETE ligne AFTER DELETE ligne
par BEFORE
INSERT BEFORE INSERT ligne AFTER INSERT ligne
ordre ON
UPDATE BEFORE UPDATE ordre AFTER UPDATE ordre
TABLE
Trigger AFTER DELETE BEFORE DELETE ordre AFTER DELETE ordre
ligne BEFORE INSERT ordre AFTER INSERT ordre
K. M. BROU Langage PL/SQL : Triggers 6
3
II. Trigger par ordre
2.1. Présentation
Exécuté une seule fois pour
l’ensemble des lignes concernées
par l’événement.
K. M. BROU Langage PL/SQL : Triggers 7
II. Trigger par ordre
2.2. Syntaxe
CREATE [OR REPLACE] TRIGGER [schéma.]nomTrigger
Séquence
Evénement [OR événement]
ON nomTable
Bloc PL/SQL ;
Séquence : BEFORE ou AFTER
Evénement : INSERT, UPDATE ou
DELETE
nomTable : nom de la table à
laquelle le déclencheur est lié
Bloc PL/SQL : décrit le traitement
à réaliser
K. M. BROU Langage PL/SQL : Triggers 8
4
II. Trigger par ordre
Exemple 1 : triggerOrdre1.sql Tester le trigger
Trigger qui affiche un message Ajouter un nouveau produit
après ajout d’un produit.
INSERT INTO Produit VALUES(76,'Serrure',1999,67);
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER ajoutProduit
AFTER INSERT ON Produit Exécution
BEGIN
DBMS_OUTPUT.PUT_LINE('Produit ajoute');
END;
/
Exécution
K. M. BROU Langage PL/SQL : Triggers 9
II. Trigger par ordre
Exemple 2 : triggerOrdre2.sql Test 1 :
Trigger qui donne à TOTO seul le Ajouter un nouveau produit
droit d’ajouter un produit.
INSERT INTO Produit VALUES(77, 'Rivet', 250, 48);
CREATE OR REPLACE TRIGGER ajoutProduit2
BEFORE INSERT ON Produit
BEGIN Erreur
IF USER != 'TOTO' THEN
RAISE_APPLICATION_ERROR(-20001,'Utilisateur ' ||
USER || ' non autorise.');
END IF;
END;
/
Exécuter ce trigger
K. M. BROU Langage PL/SQL : Triggers 10
10
5
II. Trigger par ordre
Test 2 : 1. CONNECT system/brou
alter session set "_ORACLE_SCRIPT"=true;
1. Se connecter en tant que DBA 2.
3. CREATE USER toto IDENTIFIED BY toto;
2. Créer l’utilisateur TOTO 4. GRANT CREATE SESSION TO toto;
3. Lui accorder les droits de 5. GRANT SELECT, INSERT ON BROU.Produit TO toto;
CONNECT toto/toto
connexion. 6.
7. INSERT INTO BROU.PRODUIT VALUES(77, 'DD',
4. Lui accorder des droits sur la 1999, 67);
table Produit. 8. SELECT * FROM BROU.PRODUIT;
5. Se connecter en tant que
TOTO
6. Insérer un nouveau produit
7. Vérification
K. M. BROU Langage PL/SQL : Triggers 11
11
III. Trigger ligne
3.1. Présentation
Exécuté pour chaque ligne de la
table concernée par l’exécution de
l’événement.
K. M. BROU Langage PL/SQL : Triggers 12
12
6
III. Trigger ligne
3.2. Syntaxe La valeur prise en compte dépend
de l’ordre SQL :
CREATE [OR REPLACE] TRIGGER [schéma.]nomDéclencheur
Séquence :OLD :NEW
Evénement [OR événement]
INSERT NULL Valeur créée
ON nomTable
[REFERENCING {[OLD [AS] nouveauNom] | [NEW [AS] DELETE Valeur avant NULL
nouveauNom]}] UPDATE Valeur avant Valeur après
FOR EACH ROW
[WHERE condition]
Bloc PL/SQL ; FOR EACH ROW
Pour chaque ligne
REFERENCING
Permet de changer les variable Condition
:OLD et/ou :NEW par un nouveau Expression logique utilisée pour
nom. restreindre l’action du trigger à
:OLD : valeur du champ avant certaines lignes.
modification : ordres DELETE et Trigger exécuté lorsque sa valeur
UPDATE est vraie.
:NEW : nouvelle valeur du champ : Ne doit pas contenir de requête
ordres INSERT et UPDATE. SQL
K. M. BROU Langage PL/SQL : Triggers 13
13
III. Trigger ligne
Exemple 1 : triggerligne1.sql Exécution
Vérifier que la quantité en stock
d’un produit reste toujours
positive.
CREATE OR REPLACE TRIGGER verifQteStock
BEFORE UPDATE OF qteStock ON Produit
FOR EACH ROW
BEGIN
IF :NEW.qteStock < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Mise à jour refusee');
END IF; Remaqrue :
END; :NEW et :OLD interdits dans un
/ trigger par ordre.
FOR EACH ROW
Pour chaque ligne
:NEW.qteStock
Nouvelle quantité en stock
K. M. BROU Langage PL/SQL : Triggers 14
14
7
III. Trigger ligne
Test UPDATE produit SET qteStock =
UPDATE produit SET qteStock = - 12 WHERE numProd=1;
20 ;
Modification acceptée
Message d’erreur
K. M. BROU Langage PL/SQL : Triggers 15
15
III. Trigger ligne
3.3. Un même trigger pour
plusieurs événements
Prédicats prédéfinis INSERTING,
UPDATING ou DELETING
Syntaxe
CREATE OR REPLACE TRIGGER nomTrigger
BEFORE/AFTER INSERT OR UPDATE OR DELETE ON …
BEGIN
IF INSERTING THEN … END IF;
IF UPDATING(nomColonne) THEN … END IF;
IF DELETING THEN … END IF;
…
END ;
/
K. M. BROU Langage PL/SQL : Triggers 16
16
8
III. Trigger ligne
Exemple 2 : triggerligne2.sql IF INSERTING OR UPDATING THEN
SELECT qteStock INTO qs 2
Mise à jour de la quantité en stock FROM Produit p
d’un produit WHERE p.numProd = :NEW.numProd;
IF qs > :NEW.qteCom THEN
CREATE OR REPLACE TRIGGER modifCom UPDATE Produit p
AFTER INSERT OR DELETE OR UPDATE ON DetailCom SET qteStock = qteStock - :NEW.qteCom
FOR EACH ROW WHERE p.numProd = :NEW.numProd;
DECLARE ELSE
qs NUMBER(4); RAISE_APPLICATION_ERROR(-20001, 'Commande
BEGIN refusee car qteStock = ' || qs || ' < ' || 'qteCom = ' ||
IF DELETING OR UPDATING THEN :NEW.qteCom );
UPDATE Produit p 1 END IF; 3
SET qteStock = qteStock + :OLD.qteCom END IF;
WHERE p.numProd = :OLD.numProd; END;
END IF; /
AFTER INSERT OR DELETE OR IF INSERTING OR UPDATING THEN
UPDATE IF qs > :NEW.qteCom THEN
IF DELETING OR UPDATING THEN :NEW.qteCom
:OLD.qteCom :NEW.numProd
:OLD.numProd RAISE_APPLICATION_ERROR
K. M. BROU Langage PL/SQL : Triggers 17
17
III. Trigger ligne
Exécution Insertion 2 : qteCom <= qteStock
2 INSERT INTO DetailCom VALUES
(18,1,seqLCNumLi.NEXTVAL,2);
IF INSERTING OR UPDATING
qteStock = qteStock - :NEW.qteCom
Insertion 1 : qteCom > qteStock
qteStock = 80 - 2 = 78
3 INSERT INTO DetailCom VALUES
(18,1,seqLCNumLi.NEXTVAL,200);
RAISE_APPLICATION_ERROR
K. M. BROU Langage PL/SQL : Triggers 18
18
9
III. Trigger ligne
Mise à jour 1 : augmenter qteCom
(passe de 3 à 13)
UPDATE DetailCom SET qteCom = 13
WHERE numCom=1 and numProd=2;
DetailCom :old.numCom :old.numProd :old.qteCom :new.qteCom
1 2 3 13
Produit :old.numProd :old.qteStock :new.qteStock
2 65 55
DELETING OR UPDATING
qteStock=qteStock+:OLD.qteCom
1 qteStock = 65 + 3 = 68
+
INSERTING OR UPDATING
2 qteStock = qteStock - :NEW.qteCom
qteStock = 68 – 13 = 55
K. M. BROU Langage PL/SQL : Triggers 19
19
III. Trigger ligne
Mise à jour 2 : diminuer qteCom
(passe de 13 à 3)
UPDATE DetailCom SET qteCom = 3
WHERE numCom=1 and numProd=2;
DetailCom :old.numCom :old.numProd :old.qteCom :new.qteCom
1 2 13 3
Produit :old.numProd :old.qteStock :new.qteStock
2 68 65
DELETING OR UPDATING
qteStock=qteStock+:OLD.qteCom
1 qteStock = 55 + 13 = 68
+ INSERTING OR UPDATING
2 qteStock = qteStock - :NEW.qteCom
qteStock = 68 – 3 = 65
K. M. BROU Langage PL/SQL : Triggers 20
20
10
III. Trigger ligne
Suppression : augmenter qteStock
(passe de 65 à 68)
DELETE FROM DetailCom WHERE
numCom=1 and numProd=2;
DetailCom :old.numCom :old.numProd :old.qteCom
1 2 3
Produit :old.numProd :old.qteStock :new.qteStock
2 65 68
DELETING OR UPDATING
1 qteStock=qteStock+:OLD.qteCom
qteStock = 65 + 3 = 68
K. M. BROU Langage PL/SQL : Triggers 21
21
III. Trigger ligne
3.4. Trigger ligne avec l’option Création de la table HProduit
AFTER CREATE TABLE HProduit
(utilisateur VARCHAR2(15),
Permet de faire des mises à jour dateMAJ DATE,
ou de gérer des historiques. numProd NUMBER(5),
designation VARCHAR2(12),
Variable OLD et NEW. prixUnit NUMBER(7,2),
Exemple : trigerLigne3 qteStock NUMBER(5)
);
Garder une trace des lignes
manipulées dans la table produit
K. M. BROU Langage PL/SQL : Triggers 22
22
11
III. Trigger ligne
Création du trigger Trigger histoProduit
AFTER
CREATE OR REPLACE TRIGGER histoProduit
AFTER UPDATE OR DELETE OR INSERT ON Produit Hproduit
FOR EACH ROW USER
BEGIN
INSERT INTO HProduit VALUES
SYSDATE
(USER, :OLD
SYSDATE, Exécuter le script
:OLD.numProd,:OLD.designation,:OLD.prixUnit,
:OLD.qteStock);
END;
/
K. M. BROU Langage PL/SQL : Triggers 23
23
III. Trigger ligne
Test TOTO : INSERT
BROU : UPDATE
Utilisateur Opération
BROU : DELETE
TOTO INSERT INTO BROU.Produit VALUES(80,
Utilisateur Opération ‘Sucre’,1000,86);
BROU UPDATE Produit SET qteStock= 20 WHERE
numProd=1;
BROU DELETE FROM Produit WHERE numProd=76; Vérification
Vérification
K. M. BROU Langage PL/SQL : Triggers 24
24
12
IV. Gestion des Triggers
3.5. Présentation Activation d’un trigger
Suppression d’un déclencheur particulier
DROP TRIGGER nomDéclencheur ; ALTER TRIGGER nomDéclencheur
ENABLE ;
Désactivation d’un trigger
Activation de tous les triggers
particulier
d’une table
ALTER TRIGGER nomDéclencheur
DISABLE ; ALTER TABLE nomTable ENABLE
ALL TRIGGER ;
Désactivation de tous les triggers
Vues du DD
d’une table
USER_TRIGGERS,
ALTER TABLE nomTable DISABLE
ALL TRIGGER ; ALL_TRIGGERS,
Activation DBA_TRIGGERS
à sa création, un triggeur est
automatiquement activé
K. M. BROU Langage PL/SQL : Triggers 25
25
IV. Gestion des Triggers
Exercice
Modifier la table HProduit afin d’y
ajouter le type d’opération
effectué :
Type = INSERT ou DELETE OU
UPDATE
Modifier en conséquence le code
du trigger HistoProduit
Tester avec les opérations
suivantes :
Utilisateur Opération
brou UPDATE Produit SET qteStock= 20 WHERE numProd=1;
brou DELETE FROM Produit WHERE numProd=76
toto INSERT INTO Produit VALUES(76, 'Serrure',1990,67);
K. M. BROU Langage PL/SQL : Triggers 26
26
13
Points à retenir
Introduction
Trigger par ordre
Trigger ligne
Trigger avec l’option AFTER
Gestion des triggers
K. M. BROU Langage PL/SQL : Triggers 27
27
Bibliographie
Livres : "Cours d’introduction Modèles
"Objet-relationnel sous Oracle8, d’interactions pour le client serveur
Modélisation avec UML", Christian et exemples d’architectures les
Soutou, Eyrolles. implantant", Gérard Florin,
Conservatoire National des Arts et
" Les bases de données Oracle
Métiers, Laboratoire CEDRIC
8i Développement Administration
Optimisation ", Roger Chapuis, "Oracle 10g sous Windows", Giles
DUNOD Briard, Edition Eyrolles 2006
" Client-Serveur, moteur SQL, "Oracle 11g Administration", Olivier
middleware et architectures HEURTEL, Edition ENI.
parallèles ", Serge MIRANDA et Anne "Programmation Objet avec Oracle,
RUOLS, Editions Eyrolles. Techniques et pratiques", 2e édition,
Christian SOUTOU, Edition Vuibert.
Supports de cours Oracle
Formation :
Programmer avec PL/SQL
Le langage SQL & l’outil SQL*Plus
K. M. BROU Langage PL/SQL : Triggers 28
28
14
Bibliographie
Webographie :
http://lifc.univ-
fcomte.fr/~lasalle/OracleV7/page1.h
tm de Marie-France Lasalle :
[email protected]
http://www.hds.utc.fr/~crozatst/ftp
/nf17/7.sgbdro.pdf#search=%22h%
C3%A9ritage%2BSQL3%22
http://www2.lifl.fr/~durif/bdd/cour
sBD/html/developpement.html
"Les aspects objet-relationnels,
d’Oracle (de la V8 à la 11g)",
Christian Soutou,
http://www.soutou.net/christian
K. M. BROU Langage PL/SQL : Triggers 29
29
15
