Introduction
Si tout comme moi, vous avez envie de gérer votre base de données ou du moins vos objets en incluant des règles bien précise, des choses que vous ne pouvez pas faire avec des contraintes, empêcher l’accès à vos objets selon certains critères, alors mettez vos lunettes (pour ce qui en porte :p ) car cet article est pour vous .
Qu’est ce qu’un Trigger ?
Un trigger ou encore un déclencheur est un bloc PL/SQL associé plus particulièrement à une table. Ce bloc s’exécutera automatiquement lorsqu’une instruction DML (insert , update , delete) sera fait sur la table. Il faut savoir qu’un trigger peut être exécuté avant ou après la modification d’une table , donc avant ou après les différents contraintes . Il y a une règles très importante à respecter Les triggers ne doivent en aucun cas être utilisés lorsque vous pouvez utiliser une contrainte. En effet cette dernière étant défini sur la table , elle est plus rapide.
Syntaxe d’un Trigger
CREATE [OR REPLACE ] TRIGGER nom_trigger
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF nom_colonne]
ON nom_table/vue
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
— requete SQL
END;
Explications
CREATE [OR REPLACE ] TRIGGER nom_trigger : Nous permet de créer /remplacer (si le nom existe) un trigger
{BEFORE | AFTER | INSTEAD OF } : Les deux premiers nous permettent de spécifier à quel moment le trigger sera déclenché et le troisième est utilisé seulement pour les VUES (les deux autres ne peuvent être utilisés avec les vues)
{INSERT [OR] | UPDATE [OR] | DELETE} : Nous permet de spécifier pour quel évènement déclencher le trigger (on peut en utiliser plusieurs).
[OF nom_colonne] : Utilisé avec les triggers de type update , nous permet de déclencher un trigger seulement si une colonne particulière est mise à jour.
[ON nom_table/vue] : Nous permet de dire pour quel table ou vue le trigger est associé.
[REFERENCING OLD AS o NEW AS n] : Ici cette clause va nous permettre tout simplement de donner un alias aux termes « OLD » et « NEW ». Le terme « OLD » nous permet de connaitre la ligne avant modification (update) ou en cours de suppression (delete) alors que le terme « NEW » nous permet de connaître la nouvelle ligne insérée (insert) ou la ligne après modification (update).
[FOR EACH ROW] : Nous permet de spécifier le trigger en tant que « Row level Trigger »(déclencheur de ligne). Un déclencheur de ligne est lancé une fois pour chaque ligne qui est affectée par l’instruction de déclenchement. (par défaut Statement Trigger)
WHEN (condition) : Utilisé avec FOR EACH ROW , le trigger s’exécutera seulement si la ligne respecte la condition.
Quelques exemples de Triggers :
Exemple 1
Nous avons deux tables : employees et employees_history toutes les deux composées des colonnes (employee_id , manager_id , first_name , last_name , pseudo et salary)
La table employees :
SELECT * FROM employees;
Oui-Oui on s’est inspiré de la légende d’Arthur et la table Ronde (Tous égaux = Tout le monde le même salaire :p ) , sauf qu’il y a eu une erreur lors de l’entrée des données et l’employé 10 se retrouve avec un salaire de 1000euros (grrrr). On va donc créer un trigger qui nous permet de garder une trace des modifications apportées à la table employees :
CREATE or REPLACE TRIGGER employees_history_trigger
BEFORE UPDATE OF salary
ON employees
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN
INSERT INTO employees_history
VALUES
(:o.employee_id, :o.manager_id, :o.first_name, :o.last_name, :o.pseudo, :o.salary);
END;
/
Et si on réparait notre erreur :p :
Update employees
SET salary = 10000
WHERE employee_id = 10 ;
(déclenchement du trigger)
La table employees_history :
SELECT * FROM employees_history ;
La table employees:
SELECT * FROM employees WHERE employee_id = 10;
Exemple 2
On va maintenant voir un exemple de trigger avec une vue (instead of).
Supposons que nous ayons une vue empviews avec les données suivantes:
Lorsque nous tenterons d’insérer une ligne dans empviews on ne pourra pas consulter la ligne insérer, on aura peut être une erreur du type :
SQL Error:
ORA-01732: data manipulation operation not legal on this view
01732. 00000 - « data manipulation operation not legal on this view »
Pour pallier à cette faille et garantir l’affichage de notre nouvelle ligne nous utiliserons un trigger instead of :
CREATE OR REPLACE TRIGGER insert_empviews_trigger
INSTEAD OF INSERT
ON empviews FOR EACH ROW
BEGIN
INSERT INTNO employees (employee_id, manager_id, first_name, last_name , pseudo , salary)
VALUES (:new.employee_id , :new.manager_id ,:new.first_name ,:new.last_name ,:new.pseudo ,:new.salary)
END;
/
INSERT INTO empview
VALUES (20, 1, ‘Samuel’, ‘Taochy’,2-10000);
SELECT * FROM empview ;
Amelioration avec ORACLE 11G
La clause FOLLOWS
Vous l’avez certainement remarqué, Oracle permet de définir plusieurs triggers sur une même table et surtout pour un même événement. Avant Oracle 11G on ne pouvait pas déterminé l’ordre d’exécution de ces triggers là, maintenant avec la clause FOLLOWS on demandera à Oracle d’exécuter ce triggers après les triggers cités avec la clause FOLLOWS.
Les Triggers composés
Un triggers composé présent plusieurs avantages, en effet les différentes sections peuvent partager des déclarations(variables , curseur …) communes.
CREATE [OR REPLACE ] TRIGGER nom_trigger
FOR {INSERT | UPDATE [OF colonne , ...] | DELETE}
ON nom_table/vue
[FOLLOWS nom_autre_triger[,...]]
[ WHEN (condition) ]
COMPOUND TRIGGER
[ declarations communes ]
[ BEFORE STATEMENT IS
[ declarations locales ]
BEGIN
instructions
END BEFORE STATEMENT]
[ BEFORE EACH ROW IS
[ declarations locales ]
BEGIN
instructions
END BEFORE EACH ROW]
[ AFTER EACH ROW IS
[ declarations locales ]
BEGIN
instructions
END AFTER EACH ROW]
[ AFTER STATEMENT IS
[ declarations locales ]
BEGIN
instructions
END AFTER STATEMENT]
END;
Hummm C’est Quoi ça ?? o_0 …
Pas de panique nous allons décortiquer tout ça ^^ :
- La clause FOR remplace la clause BEFORE/AFTER d’un trigger normal et nous permet de dire pour quel évènement déclencher le trigger.
- FOLLOWS : le trigger sera déclenché après le ou les triggers cité(s)
- Déclarations communes : permet de définir des variables, curseurs, sous-programmes communes aux différentes sections.
- Déclarations locales : permet de définir des variables, curseurs, sous-programmes locale à la section
- Le code comporte au maximum 4 sections, composé comme suit :
moment_declenchement IS
[declarations locales]
BEGIN
Instructions
END moment_declenchement
Avec moment_declenchement :
BEFORE STATEMENT / BEFORE EACH ROW / AFTER EACH ROW / AFTER STATEMENT
Exemple :
CREATE OR REPLACE TRIGGER employees_salaire_trigger
FOR update OF salary
ON employees
COUMPOUND TRIGEGER
ajout_total number := 0;
procedure message(msg VARCHAR2) IS
BEGIN
dbms_output.put_line (msg);
END message;
AFTER EACH ROW IS
difference number;
BEGIN
ajout := :new.salary – : old.salary;
message(‘Employé numéro’|| :new.employee_id ||’ : salaire augmenté de ‘|| ajout ||’ euro(s)’);
ajout_totale = ajout_totale + ajout;
END AFTER EACH ROW;
AFTER STATEMENT IS
difference number;
BEGIN
message(‘Ajout totale est de ‘|| ajout_total ||’ euro(s)’);
END AFTER STATEMENT;
END employees_salaire_trigger;
/
Ce trigger sera déclenché lors d’une mise à jour sur la colonne salaire.
Pour chaque ligne affectée , on aura un message du genre :
=> Employé numéro 100 : salaire augmenté de 10euro(s)
Et à la fin de l’exécution , nous aurons un message nous montrant le total d’argent ayant été rajouté à l’ensemble des employés.
=> Ajout total est de 100 euro(s)
Conclusion
Nous avons vu à travers cet article ce qu’est un trigger , comment en créer et surtout quelques améliorations apportées par oracle 11G. Je tenais juste à vous dire qu’il existe aussi des triggers sur des événements systèmes ou utilisateurs qui nous permettent d’améliorer la gestion de notre base de données avec des attributs qui peuvent être utilisé pour identifier l’origine d’un évenement.
Si vous avez une question ou une remarque, n’hésiter pas. Je reste à l’écoute et prêt à répondre 
Jeu de briques
Snake
Pacman
Bubble