Magazine Informatique

Oracle: les triggers

Publié le 14 mai 2012 par Lolokai @lolokai

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;

Oracle: les triggers

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 ;

Oracle: les triggers

La table employees:

SELECT * FROM employees WHERE employee_id = 10;

Oracle: les triggers

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:

Oracle: les triggers

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 ;

Oracle: les triggers

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 :)


Retour à La Une de Logo Paperblog

A propos de l’auteur


Lolokai 189 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte l'auteur n'a pas encore renseigné son compte

Dossier Paperblog