Fondements des bases de données
Programmation en PL/SQL Oracle – les déclencheurs
Équipe pédagogique BD
[Link]
Version du 13 octobre 2016
Déclencheurs (triggers)
Les déclencheurs (triggers)
I Les contraintes prédéfinies ne sont pas toujours suffisantes
Ex : Tout nouveau prix d’un produit doit avoir une date de début
supérieure à celle des autres prix pour ce produit
I Exécuter des actions lors de certains événements :
I AFTER ou BEFORE
I INSERT, DELETE ou UPDATE
I FOR EACH ROW
I non (STATEMENT) : exécuté une seule fois pour la commande.
I oui (ROW) : exécuté à chaque ligne concernée.
Syntaxe générale
CREATE [OR REPLACE ] TRIGGER t r i g g e r _ n a m e
{BEFORE | AFTER} {INSERT [OR] | UPDATE [OR] | DELETE}
[ OF col_name ] ON t a b l e _ n a m e
[ REFERENCING OLD AS o NEW AS n ]
[ FOR EACH ROW]
WHEN ( c o n d i t i o n )
BEGIN
−−− s q l s t a t e m e n t s
END;
Utilisation des déclencheurs
Cas d’usage
I Logging et audit (enregistrement des modifications)
I Contrôle d’accès
I Application de règles métier
Problèmes d’utilisation
I Ne jamais simuler de fonctionnalités existantes
I Foreign keys
I Primary keys
I Checks
I Attributs virtuels
I Vues
I Réplication
I ...
I Flot d’exécution difficile à suivre
I Maintenabilité du code
Accès aux valeurs modifiées
Utilisation de :NEW et :OLD
I Si nous ajoutons un client dont le nom est toto alors nous
récupérons ce nom grâce à la variable :[Link]
I Dans le cas de suppression ou modification, les anciennes valeurs
sont dans la variable :[Link]
Exemple
Archiver le nom de l’utilisateur, la date et l’action effectuée dans une
table LOG_EMP lors de l’ajout d’un clients dans la table EMP
I Créer la table LOG_EMP avec la même structure que EMP.
I Ajouter les colonnes LOGGIN, DATE_M, ACTION.
I Créer un trigger AFTER INSERT ON emp
Accès aux valeurs modifiées
Exemple
CREATE o r REPLACE TRIGGER l o g a d d
AFTER INSERT
ON emp
FOR EACH ROW
BEGIN
INSERT INTO log_emp VALUES(
: new . matr ,
...
USER ,
SYSDATE ,
’ INSERT ’ ) ;
END;
/
INSERT INTO EMP VALUES
( 3 , ’ T a r j a n , ␣ R o b e r t ’ , 2 4 4 6 . 1 3 , ’ P r i n c e t o n , ␣US ’ , 2 ) ;
SELECT ∗ FROM log_emp ;
Bug de sqldeveloper Enter bindings : exécuter le script en intégralité
dans un nouveau fichier. . .
Prédicats conditionnels
I Lorsqu’un trigger a plusieurs opérations déclenchantes le corps peut
avoir des prédicats conditionnels :
I IF INSERTING THEN ... END IF;
I IF UPDATING THEN ... END IF;
I On peut préciser les colonnes soumises aux opérations déclenchantes
avec OF
I On peut préciser une condition de test dans le WHEN
Exemple
CREATE OR REPLACE TRIGGER t _ e m p _ s a l _ i n c r e a s e
BEFORE UPDATE OF s a l , dep
ON EMP
FOR EACH ROW
WHEN (NEW. dep = 1 )
BEGIN
I F UPDATING( ’ s a l ’ ) THEN
I F ( :NEW. s a l < : OLD . s a l ) THEN
r a i s e _ a p p l i c a t i o n _ e r r o r ( −20000 , ’ S a l l a r y ␣ c a n n o t ␣ d e c r e a s e ’ ) ;
END I F ;
END I F ;
END;
Important
Interdictions
I Les commandes de définition de données (LDD)
I les commandes de contrôle de transactions (ROLLBACK, COMMIT)
Ne doivent pas être utilisées dans le corps d’un trigger.
Remarques
I Pour éviter une modification de données dans un trigger BEFORE, il
faut lever une exeception.
I Le dictionnaire de données a des vues sur les triggers :
USER_TRIGGERS, ALL_TRIGGERS, DBA_TRIGGERS.
I La commande DROP permet de supprimer un trigger.
Tables mutantes et contraignantes
I Une table mutante est une table en cours de modification par une
opération déclenchante (UPDATE, DELETE, INSERT) ou l’effet de
DELETE CASCADE provenant de cette opération.
I Une table contraignante est une table qu’une opération déclenchante
doit lire, soit directement via une commande SQL (UPDATE SET ...
WHERE) ou indirectement pour une contrainte d’intégrité référentielle.
Les commandes SQL dans le corps d’un trigger ne peuvent pas
I Lire (par une requête) ou modifier un table mutante d’une opération
déclenchante.
I Changer des valeurs sur les colonnes de clés (PRIMARY, FOREIGN,
UNIQUE) d’une table contraignante.
Ces restrictions permettent d’éviter la consultation d’une table dans un
état transitoire et donc incohérent.
Exemple d’erreur
CREATE OR REPLACE TRIGGER emp_count
AFTER DELETE ON emp
FOR EACH ROW
DECLARE
n integer ;
BEGIN
SELECT COUNT( ∗ ) INTO n
FROM emp ;
dbms_output . p u t _ l i n e (
’On␣ a ␣ ’ | | n | | ’ ␣ employes ␣ dans ␣ l a ␣ base ’ ) ;
END;
DELETE FROM emp WHERE matr = 3 ;
ORA-04091: table [Link] is mutating, trigger/function
may not see it.
Dans ce cas là, il ne faut pas utiliser FOR EACH ROW pour pouvoir
déterminer la valeur du SELECT COUNT(*) FROM emp;.
Fin du cours.