Les Triggers PLSQL

Najib Tounsi

Lien permanent http://www.mescours.ma/DB/Polys/SQL/Exercices/PL-SQL-2-Triggers.html

Sommaire

  1. Introduction
  2. Trigger simple
  3. Usage de for each row
  4. Usage de old et new et clause when
  5. 1er résumé sur les triggers
  6. Activer/désactiver un trigger
  7. Autres exemples

Introduction

Les triggers ou déclencheurs, sont des procédures qui sont lancées automatiquement suite à un événement, généralement une mise à jour de données. Ils servent à lancer automatiquement d'autres actions comme une propagation de mise à jour pour maintenir une cohérence, une alerte ou une vérification d'intégrité, etc. On dit déclencher un trigger (ang. fire).

Un exemple de trigger, est l'incrémentation de 1 de l'effectif des employés d'un département suite à l'insertion dans la base de donnée d'un employé travaillant dans ce département. Un autre exemple est la propagation de mise à jour pour l'intégrité référentielle.

En PLSQL, un trigger est un programme PLSQL exécuté quand un événement arrive. L'événement est en générale une instruction INSERT, UPDATE ou DELETE. Mais cela peut être aussi une commande LDD, un login/logout d'un utilisateur etc. Dans le cas d'une mise à jour, le programme peut se déclencher soit avant que la mise à jour a lieu, soit après.

Un trigger est donc composé de deux parties : une entête introduisant l'événement déclencheur et un  corps contenant les instructions pour l'action à exécuter. 

En PLSQL, la forme générale d'un trigger est : 

CREATE TRIGGER   nom du trigger
BEFORE ou AFTER  événement déclencheur ON table mise à jour
[ ... options supplémentaires...]

[ DECLARE
    déclarations éventuelles ]
BEGIN
    instructions à éxecuter
[ EXCEPTION
    prise en compte des exceptions éventuelles]
END;

Dans les exemples qui suivent l'action d'un trigger est juste une alerte sur la console pour vérifier que le programme a bien eu lieu.

Trigger simple

Exemple 1) Le trigger suivant  affiche un message après l'insertion d'un employé

create or replace trigger monTrigger1
 after 
    insert on employee
 begin
    dbms_output.put_line('OK');
 end;
 /

Test avec un insert :

SQL> set serveroutput on  /* pour la sortie console */
SQL> insert into employee (enum) values ('E11');
OK                         <-- affiché par le trigger

1 row created.             <-- affiché par Oracle SQLPlus

où on voit que l'action du trigger est lancée (affichage 'OK')  suite à la mise à jour des données de la base (1 row created). C'est un effet de bord de la mise à jour.

Forme simple de création d'un trigger :

CREATE [or replace] TRIGGER nom du trigger
AFTER ou BEFORE
  INSERT ou UPDATE ou DELETE ON la table touchée
BEGIN
        corps du trigger
END;

(ici corps avec juste la partie begin end;)

Exemple 2) Un trigger de mise à jour

create or replace trigger monTrigger2
 after  
    update on employee
   begin 
      dbms_output.put_line('OK');
   end; 
 /

Requêtes update (sans changement vraiment) pour lancer le trigger :

SQL>  update employee set salary = salary where dept='D1';
OK

3 rows updated.

SQL>  update employee set address = address where dept='D2';
OK

2 rows updated.

où on voit que l'action du trigger est lancée pour la mise à jour d'une donnée de la relation employee (adresse et salaire ici.)

On peut remarquer ici que le trigger s'exécute à cause l'opération update vue dans son ensemble, et indépendamment de quels tuples touchés par la mise  jour (la clause where actuelle) ou de leur nombre.

On pourrait souhaiter raffiner le déclenchement du trigger en indiquant quel est l'attribut concerné par une mise à jour.

Exemple 3) Un trigger de mise à jours d'attribut spécifié :

create or replace trigger monTrigger3
 after  
    update of salary on employee
   begin 
      dbms_output.put_line('OK');
   end; 
 /

Mêmes requêtes update que précédemment :

SQL>  update employee set salary = salary where dept='D1';
OK

3 rows updated.

SQL>  update employee set address = address where dept='D2';

2 rows updated.

Seule la mise à jour sur le salaire a lancé le trigger. Pas de message 'OK' à la deuxième requête update qui concerne l'adresse.

NB. Pour connaître la liste des triggers créés, consulter la table USER_TRIGGER.

SQL> select trigger_name from user_triggers;

TRIGGER_NAME
------------------------------
MONTRIGGER3
MONTRIGGER2
MONTRIGGER1

Autres attributs intéressants TRIGGER_TYPE et TRIGGERING_EVENT, respectivement type de trigger (each row ou non) et événement déclencheur (update/insert/delete). Utiliser describe user_triggers pour les autres attributs.

Exercices :

  1. Créer un trigger qui affiche un message lors de la mise à jour de l'adresse d'un employé.
  2. Vérifier que le résultat est indifférent à before ou after. (Il y a une différence système cependant...)
  3. Commettre une erreur d'exécution (par exemple 'OK'/2, erreur conversion) et tester. La mise à jour qui déclenche le trigger a-t-elle eu lieu?
  4. Traiter cette exception (avec when others) pour afficher un message quelconque, et  constater que la mise à jour a lieu cette fois-ci.

Usage de for each row

Dnas l'exemple 2 ci-dessus, on a vue que le trigger est lancée pour l'opération globalement et indépendamment d

On peut souhaiter qu'une fois lancé, un trigger exécute son code pour chaque tuple touché par la mise à jour. Par exemple pour consulter le valeur des attributs touchés par la mise à jour ou d'autres valeurs.

On utilise alors la clause for each row.

Exemple 4)

create or replace trigger monTrigger4
before update of salary on employee
for each row
begin
   dbms_output.put_line('OK');
end;
/

Le  code du trigger devrait s'exécuter autant de fois que de lignes touchées

SQL> update employee set salary = salary where dept='D1';
OK
OK
OK

3 rows updated.

Pouvoir exécuter un trigger sur chaque tuple touché par une mise à jour est intéressant, car on peut accéder aux valeurs des champs touchés par la mise à jours. Valeurs avant et après la mise à jour. Préfixes old et new.

Usage de old et new et clause when

Exemple 5) Après augmentation des salaires des employés, afficher les salaires avant et après.

create or replace trigger monTrigger5
after update of salary on employee
for each row
begin
   dbms_output.put_line('Nouveau: ' || :new.salary);
   dbms_output.put_line('Ancien: ' || :old.salary);
end;

Ce qui donne

SQL> update employee set salary = salary + 450 where dept='D3';
Nouveau: 8450
Ancien: 8000
Nouveau: 5450
Ancien: 5000

2 rows updated.

Exercice : Tester new et old avec insert et delete. (ou bien old ou bien new n'a pas de valeur)

Clause when

Cette clause permet de filtrer sur quels tuples sera exécuté le corps du trigger.

Exemple 6) Même exemple, mais on n'affiche que les salaires inférieurs à smic.

create or replace trigger monTrigger5
after update of salary on employee
for each row
when (old.salary < &smic)
begin
   dbms_output.put_line('Nouveau: ' || :new.salary);
   dbms_output.put_line('Ancien: ' || :old.salary);
end;
/
Enter value for smic: 5001

Exécution :

SQL> update employee set salary = salary + 450 where dept='D3';
Nouveau: 5450
Ancien: 5000

2 rows updated.

A noter:

  1. Le trigger est lancé par la mise à jour update.
  2. La mise à jour a concerné 2 tuples
  3. Un seul a répondu à la condition when (salaire < 5001)

Noter aussi la syntaxe old dans when sans (:)

1er résumé sur les triggers

Un trigger se compose donc de trois partie :

@@@

Activer/désactiver un trigger

On peut avoir besoin de déactiver momentanément un trigger pour effectuer une certaine tâche (e.g.  un test) sans le déclencher. On utilise la commande alter trigger avec les option disable / enable :

Un trigger est activé dès sa création.

Exercices :

  1. Créer un trigger qui calcule et affiche la nouvelles moyenne des salaire après chaque changement de salaire.
  2. @@@

Autres exemples

Nous avons vu des triggers "alertes console". L'intérêt d'un trigger  est de répercuter automatiquement d'autres actions (mises à jour) suite à l'action qui l'a déclenché.

Exemple 7) Après l'insertion d'un employé, il faut augmenter de 1 l'effectif du département où il/elle travaille. Logique.

create or replace trigger monTrigger6
after insert on employee
for each row
begin
   dbms_output.put_line('Un nouveau au département: ' || :new.dept);
   update department set nbemp = nbemp + 1
   where dnum = :new.dept;
end;

Exécution :

SQL> insert into employee  values ('E14', 'Aziza', 6700, 'Rabat', 'D1');
Numéro nouveau: E14

1 row created.

SQL> select * from employee;

ENUM  ENAME                    SALARY ADDRESS    DEPT
----- -------------------- ---------- ---------- -----
E14   Aziza                      6700 Rabat      D1
...

9 rows selected.

SQL> insert into employee  values ('E14', 'Aziza', 6700, 'Rabat', 'D1');
Un nouveau au département: D1

1 row created.                                          <-- insertion effectuée

SQL> select * from department where dnum='D1';

DNUM  DNAME           FLOOR NBEMP MGR
----- --------------- ----- ----- -----
D1    Jouets              1     4 E1                       <-- augmenté à 4

Exemple 7) Au changement d'un numéro d'un département (attribut dnum relation départment),  changer aussi aussi l'attribut dept de la relation employee.

create or replace trigger monTrigger7
after update of dnum on department
for each row
declare 
   cursor empCur is
   select enum, dept from employee
   where dept = :old.dnum 
   for update of dept;

begin
/* open empcur;  
dbms_output.put_line(empCur%ROWCOUNT);*/
   for e in empcur loop
       dbms_output.put_line( 'Changement de '|| e.dept ||
                             ' pour '||e.enum);
       update employee set dept = :new.dnum
       where dept= :old.dnum;
   end loop;
end;

Explication @@@

Exécution :

SQL> select * from employee where dept ='D3';

ENUM  ENAME                SALARY ADDRESS    DEPT
----- -------------------- ------ ---------- -----
E5    Amina                  8450 Fes        D3
E4    Said                   5450 Fes        D3

SQL> update department set dnum = 'D6' where dnum = 'D3';
Changement de D3 pour E5
Changement de D3 pour E4

1 row updated.

SQL> select * from employee where dept ='D6';

ENUM  ENAME                SALARY ADDRESS    DEPT
----- -------------------- ------ ---------- -----
E5    Amina                  8450 Fes        D6
E4    Said                   5450 Fes        D6

Remarque : On peut reconnaître ici la contrainte d'intégrité référentielle. Cette contrainte est normalement satisfaite par les déclarations de foreign key, casacade, set null  etc. , que par un trigger.

Exercices :

  1. Vérifier le changement de clé d'un département où aucun employé ne travaille (D5 ici).
  2. Définir un trigger qui traite de la suppression d'un département. (Mettre à null le champ Dept dans la table employee)

Correction :

create or replace trigger monTrigger7-bis
after delete on department
for each row
declare 
   cursor empCur is
   select enum, dept from employee
   where dept = :old.dnum 
   for update of dept;

begin
   for e in empcur loop
       dbms_output.put_line( 'Changement de '|| e.dept ||
                             ' pour '||e.enum);
       update employee set dept = null;
       where dept= :old.dnum;
   end loop;
end;
  1. Créer un trigger qui calcule et affiche la nouvelles moyenne des salaire après chaque changement de salaire.
  2. Créer une table
        historique des salaire (date, ancien salaire, nouveau salaire, numéro employé)
    qui est remplie à chaque changement de salaire d'un employé. Utiliser sysdate pour la date du jour.

Arrêt d'une transaction dans un trigger

Exemple 8) Comment stopper une mise à jour dans un trigger déclenché parv une màj  qui viole une contrainte?

Soit la contrainte  "nouveau salaire toujours supérieur à l'ancien".  On va utiliser

raise_application_error (codeErreur, message)

de oracle pour arrêter la transaction.

create or replace trigger monTriggerErr
after update of salary on employee
for each row
when (old.salary > new.salary)
begin
   dbms_output.put_line('Nouveau: ' || :new.salary);
   dbms_output.put_line('Ancien: ' || :old.salary);
    raise_application_error (-20001,'new salary < old salary');
end;
/

Bloc PLSQL qui fait la màj et qui test SQLCODE

begin 
   update employee set salary = salary -1 where enum = 'E1';
   if SQLCODE = -20001 then
       dbms_output.put_line(SQLERRM);
       rollback;  -- normalement automatique avec raise_error
   end if;
 end;
/

Nouveau: 7299
Ancien: 7300
begin
*
ERROR at line 1:
ORA-20001: new salary < old salary
... autres messages ...

SQL> select * from employee where enum = 'E1';

ENUM  ENAME                SALARY ADDRESS    DEPT
----- -------------------- ------ ---------- -----
E1    Ali                    7300 Fes        D1           <-- màj non faite

Ici, le update est exécuté, le trigger lancé qui vérifie si le nouveau salaire est inférieur à l'ancien, auquel cas il soulève l'erreur -20001 et le message associé. Cette erreur -20001 est ensuite récupérée et testée dans SQLCODE afin d'effectuer un rollback et défaire la transaction.