Un meilleur job mieux payé ?

Deviens chef de projet, développeur, ingénieur, informaticien

Mets à jour ton profil pro

ça m'intéresse

Les déclencheurs LMD dans OracleDatabase11g

Contrôlez l'ordre d'exécution de vos déclencheurs et évitez l'erreur "table en mutation" à l'aide d'un déclencheur composé dans la base de données Oracle 11g

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

1. Introduction

Ce tutoriel montre comment utiliser quelques concepts relatifs aux déclencheurs introduits par oracle11g, tels que la clause FOLLOWS, les déclencheurs composés ou encore comment éviter l'erreur de table en mutation en utilisant un déclencheur composé.

2. La clause FOLLOWS

Avant Oracle 11g, il n'est pas possible de définir l'ordre d'exécution des déclencheurs de même type.

Dans le cas où l'ordre est important, il est possible de grouper toutes les opérations dans un seul déclencheur.

Grace à la clause FOLLOWS introduite par Oracle 11g, il est maintenant possible de spécifier ou contrôler l'ordre d'exécution de ce type de déclencheurs.

La clause FOLLOWS utilisée lors de la création d'un déclencheur X indique que l'exécution de ce déclencheur X suivra toujours celle du déclencheur Y spécifié après cette clause.

Le déclencheur spécifié après la clause FOLLOWS doit être préalablement créé et compilé avec succès.

3. Activer, désactiver un déclencheur au moment de sa création

Avant Oracle 11g, vous pouvez seulement créer un déclencheur avec le statut activé.
Par la suite, il vous est possible de le désactiver ou encore l'activer en utilisant les commandes :

ALTER TRIGGER nom_declencheur ENABLE
ALTER TRIGGER nom_declencheur DISABLE

Avec Oracle 11G, il est possible d'activer ou désactiver un déclencheur au moment de sa création, l'option ENABLE étant l'option par défaut.

Créons un déclencheur avec l'option DISABLE, nommé TRIGGER MUT_TAB_TRIGGER qui empêche toute mise à jour de notre table EMPLOYEES dans le cas où le nouveau nom est plus court que la moyenne des noms.

Création du déclencheur MUT_TAB_TRIGGER avec le statut désactivé
Sélectionnez

CREATE OR REPLACE TRIGGER MUT_TAB_TRIGGER
  BEFORE UPDATE ON EMPLOYEES
  FOR EACH ROW 
  DISABLE
DECLARE
  V_AVG_LENTGH PLS_INTEGER;
 
BEGIN
 
  SELECT ROUND(AVG(LENGTH(FIRST_NAME)))
    INTO V_AVG_LENTGH
    FROM EMPLOYEES ;
 
  IF LENGTH(:NEW.FIRST_NAME) > V_AVG_LENTGH THEN
    DBMS_OUTPUT.PUT_LINE('Mise à jour effectuée');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Le nouveau nom est plus court que la moyenne des noms');
  END IF;
 
END MUT_TAB_TRIGGER;


Interrogeons la vue USER_TRIGGERS pour vérifier le statut de notre déclencheur.

 
Sélectionnez

SQL> SELECT TRIGGER_NAME, STATUS
  2    FROM USER_TRIGGERS
  3   WHERE TRIGGER_NAME = 'MUT_TAB_TRIGGER'
  4  /
 
TRIGGER_NAME                   STATUS
------------------------------ --------
MUT_TAB_TRIGGER                DISABLED

4. Table en mutation

Une table en mutation est une table sur laquelle un changement est entrain de se faire.

Ce changement peut provenir d'une opération dml (insert - update - delete) ou encore de la commande delete cascade constraints.

Cette erreur est rencontrée seulement dans les déclencheurs de niveau enregistrement.

Vous ne pouvez interroger ou modifier une table pendant qu'elle est en mutation. A cause du changement en cours, son état devient (instable - inconsistant)
et les pseudo-colonnes new et old ne sont pas accessibles.

Illustration


Essayons d'illustrer ceci par un exemple très simple. Considérons notre table EMPLOYEES sur laquelle nous voulons modifier quelques lignes.
Pour cela, activons notre déclencheur TRIGGER MUT_TAB_TRIGGER.

Activons notre déclencheur MUT_TAB_TRIGGER

 
Sélectionnez

SQL> ALTER TRIGGER MUT_TAB_TRIGGER ENABLE
  2  /
 
Trigger altered


Modifions nos trois premières lignes

 
Sélectionnez

SQL> UPDATE EMPLOYEES
  2     SET FIRST_NAME = FIRST_NAME || ' - ' || SUBSTR(LAST_NAME, 1, 1)
  3   WHERE ID IN (1, 2, 3)
  4  /
 
UPDATE EMPLOYEES 
   SET FIRST_NAME = FIRST_NAME || ' - ' || SUBSTR(LAST_NAME, 1, 1)
 WHERE ID IN (1, 2, 3)
 
ORA-04091: la table SALEM.EMPLOYEES est en mutation ; le déclencheur ou la fonction ne peut la voir
ORA-06512: à "SALEM.MUT_TAB_TRIGGER", ligne 6
ORA-04088: erreur lors d'exécution du déclencheur 'SALEM.MUT_TAB_TRIGGER'


Vu que dans le corps de notre déclencheur TRIGGER MUT_TAB_TRIGGER, nous avons interrogé notre table EMPLOYEES, l'erreur de table en mutation est survenue.

5. Les déclencheurs composés

Introduit par Oracle 11g, ce type de déclencheur agit à la fois comme déclencheur de niveau table et déclencheur de niveau ligne.
Il est déclenché par une opération dml (insert - update - delete), c'est en fait un déclencheur dml.

Permet de capturer l'information aux quatre points d'exécution :

  • Avant changements survenus au niveau table.
  • Avant changements survenus au niveau de chaque enregistrement.
  • Après changements survenus au niveau de chaque enregistrement.
  • Après changements survenus au niveau table.

Toutefois, un déclencheur composé ne supporte pas :

La clause WHEN
La directive PRAGMA AUTONOMOUS_TRANSACTION
La section d'exception

Par contre, la section d'exception peut être définie au niveau de chaque bloc relatif au moment d'exécution.

Le déclencheur composé n'est pas exécuté si l'opération dml n'affecte aucun enregistrement et ( BEFORE STATEMENT et AFTER STATEMENT ) ne sont pas définis.

Les déclencheurs composés pour éviter l'erreur "table en mutation"

Création du déclencheur composé MUT_COMP_TRIGGER
Sélectionnez

CREATE OR REPLACE TRIGGER MUT_COMP_TRIGGER
  FOR UPDATE OR DELETE ON EMPLOYEES 
COMPOUND TRIGGER
-- déclarations globales
G_TOT_LIG           PLS_INTEGER := 0; -- Taille de la table EMPLOYEES
G_AVG_LENTGH        PLS_INTEGER;      -- Longueur moyenne de la colonne FIRST_NAME
G_TOT_LIG_MOD       PLS_INTEGER := 0; -- Total de lignes modifiées
 
  BEFORE STATEMENT IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Début BEFORE STATEMENT');
  -- Récupérer le nombre de lignes de notre table EMPLOYEES
  SELECT COUNT(ID) INTO G_TOT_LIG FROM EMPLOYEES;
 
  DBMS_OUTPUT.PUT_LINE('Taille de la table EMPLOYEES ' || G_TOT_LIG);
  -- Récupérer la longueur moyenne de la colonne FIRST_NAME
  SELECT ROUND(AVG(LENGTH(FIRST_NAME)))
    INTO G_AVG_LENTGH
    FROM EMPLOYEES;
  DBMS_OUTPUT.PUT_LINE('Longueur moyenne de la colonne FIRST_NAME '
                                      || G_AVG_LENTGH);
  DBMS_OUTPUT.PUT_LINE('Fin BEFORE STATEMENT');
  DBMS_OUTPUT.PUT_LINE('---------------------');
END BEFORE STATEMENT;
 
BEFORE EACH ROW IS 
BEGIN 
  DBMS_OUTPUT.PUT_LINE('Début BEFORE EACH ROW');
  DBMS_OUTPUT.PUT_LINE('Ancien nom  :   ' || :OLD.FIRST_NAME);
  DBMS_OUTPUT.PUT_LINE('Fin BEFORE EACH ROW');
  DBMS_OUTPUT.PUT_LINE('---------------------');
END BEFORE EACH ROW;
 
AFTER EACH ROW IS 
BEGIN 
  DBMS_OUTPUT.PUT_LINE('Début AFTER EACH ROW'); 
  DBMS_OUTPUT.PUT_LINE('Ancien nom  :' || :OLD.FIRST_NAME 
                                       || ' Nouveau nom : ' 
                                       || :NEW.FIRST_NAME 
                                       || CHR(10));
 
    IF LENGTH(:NEW.FIRST_NAME) > G_AVG_LENTGH THEN 
        DBMS_OUTPUT.PUT_LINE('Ancien nom  :' || :OLD.FIRST_NAME 
                                             || ' Nouveau nom : ' 
                                             || :NEW.FIRST_NAME 
                                             || CHR(10));
 
        G_TOT_LIG_MOD := G_TOT_LIG_MOD + 1;
 
    ELSE
 
        DBMS_OUTPUT.PUT_LINE('Nouveau nom ( ' || :NEW.FIRST_NAME 
                                              || ' )' 
                                              || 'a pour longueur ' 
                                              || LENGTH(:NEW.FIRST_NAME) 
                                              || ' < =  Moyenne ' 
                                              || G_AVG_LENTGH); 
        RAISE_APPLICATION_ERROR(-20001, 'Le nouveau nom est plus court que la moyenne des noms'); 
    END IF; 
  DBMS_OUTPUT.PUT_LINE('Fin AFTER EACH ROW'); 
  DBMS_OUTPUT.PUT_LINE('---------------------'); 
END AFTER EACH ROW;
 
--AFTER STATEMENT
AFTER STATEMENT IS 
BEGIN 
  DBMS_OUTPUT.PUT_LINE('Début AFTER STATEMENT'); 
  DBMS_OUTPUT.PUT_LINE('Total des lignes modifiées :  ' || G_TOT_LIG_MOD); 
  DBMS_OUTPUT.PUT_LINE('Fin AFTER STATEMENT'); 
END AFTER STATEMENT;
 
END MUT_COMP_TRIGGER;


Essayons de faire une mise à jour avec des noms plus courts que la moyenne des noms.

 
Sélectionnez

SQL> UPDATE EMPLOYEES
  2     SET FIRST_NAME = SUBSTR(FIRST_NAME, 1, LENGTH(FIRST_NAME) - 1)
  3   WHERE ID IN (1, 2, 3);
 
UPDATE EMPLOYEES
   SET FIRST_NAME = SUBSTR(FIRST_NAME, 1, LENGTH(FIRST_NAME) - 1)
 WHERE ID IN (1, 2, 3)
 
ORA-20001: Le nouveau nom est plus court que la moyenne des noms
ORA-06512: à "SALEM.MUT_COMP_TRIGGER", ligne 56
ORA-04088: erreur lors d'exécution du déclencheur 'SALEM.MUT_COMP_TRIGGER'


Essayons de faire une mise à jour avec des noms plus longs que la moyenne des noms.

 
Sélectionnez

SQL>  UPDATE EMPLOYEES
  2      SET FIRST_NAME = FIRST_NAME || ' - ' || SUBSTR(LAST_NAME, 1, 3)
  3    WHERE ID IN (1, 2, 3);
 
3 rows updated
 
SQL> SELECT * FROM EMPLOYEES;
 
        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Marlene - The        Theriault
         2 Rachel - Car         Carmichael
         3 James - Vis          Viscusi
         4 Michael              Abbey
         5 Michael              Corey


Contrairement à l'exécution du déclencheur MUT_TAB_TRIGGER qui provoque l'erreur de "table en mutation",
le déclencheur composé MUT_COMP_TRIGGER s'exécute normalement et évite ce type d'erreur.

6. Exécution des déclencheurs LMD de même type dans la base de données Oracle 10 G

Commençons par définir trois déclencheurs de niveau enregistrement, nommés Trig_AfterDelete1, Trig_AfterDelete2 et Trig_AfterDelete3 sur notre table EMPLOYEES.
Tous ces déclencheurs sont du même type (ont exactement le même corps) et sont créés dans cet ordre : Trig_AfterDelete1 créé avant Trig_AfterDelete2 qui lui, est créé avant le déclencheur Trig_AfterDelete3.

Tous ces déclencheurs sont exécutés dès lors qu'une opération de suppression est effectuée sur notre table EMPLOYEES.

Chaque déclencheur, une fois exécuté, insère les lignes supprimées ainsi que quelques informations supplémentaires telles que le nom du déclencheur et son ordre d'exécution.

Quand nous interrogeons la vue USER_OBJECTS du dictionnaire de données, nous remarquons que tous les déclencheurs sont exécutés dans l'ordre inverse de leur date de création.

Le premier déclencheur créé est exécuté en dernier et le dernier déclencheur créé est exécuté en premier.

Les scripts suivants sont exécutés dans la base de données Oracle10g

Création de la table EMPLOYEES
Sélectionnez

SQL> CREATE TABLE EMPLOYEES (
  2         ID          NUMBER,
  3         FIRST_NAME  VARCHAR2 (20),
  4         LAST_NAME   VARCHAR2 (20)
  5)
  6 /
Table created


Insertion dans la table EMPLOYEES de quelques lignes qui serviront pour nos tests.

Insertion de quelques lignes dans la table EMPLOYEES
Sélectionnez

INSERT INTO EMPLOYEES VALUES (1, 'Marlene', 'Theriault');
INSERT INTO EMPLOYEES VALUES (2, 'Rachel', 'Carmichael');
INSERT INTO EMPLOYEES VALUES (3, 'James', 'Viscusi');
INSERT INTO EMPLOYEES VALUES (4, 'Michael', 'Abbey');
INSERT INTO EMPLOYEES VALUES (5, 'Michael', 'Corey');
COMMIT;


Nous allons créer une table AUDIT_EMPLOYEES qui va servir à enregistrer les changements survenus dans notre table EMPLOYEES.

Création de la table AUDIT_EMPLOYEES
Sélectionnez

SQL> create table AUDIT_EMPLOYEES
  2  (
  3    ID              NUMBER,
  4    FIRST_NAME      VARCHAR2(20),
  5    LAST_NAME       VARCHAR2(20),
  6    EXEC_ORDER      INTEGER,
  7    TRIG_FIRED      VARCHAR2(20)
  8  )
  9  /
 
Table created

Visualisons le contenu de la table EMPLOYEES

 
Sélectionnez

SQL> SELECT *
  2  FROM EMPLOYEES
  3  WHERE ID < 6
  4  /
  ID                FIRST_NAME                          LAST_NAME
 ----------       ---------------------                ---------------------
  1                   Marlene                           Theriault
  2                   Rachel                            Carmichael
  3                   James                             Viscusi
  4                   Michael                           Abbey
  5                   Michael                           Corey

Nous avons besoin d'une variable globale qui enregistre l'ordre d'exécution de chaque déclencheur dans la table AUDIT_EMPLOYEES.

 
Sélectionnez

CREATE OR REPLACE PACKAGE PKG_ORDER AS
  G_ORDER INTEGER := 0;
END PKG_ORDER;

Maintenant, nous allons créer nos déclencheurs dans l'ordre qui suit:TRG_AFTERDELETE1 avant TRG_AFTERDELETE2 avant TRG_AFTERDELETE3.

Déclencheur TRG_AFTERDELETE1

Création du déclencheur TRG_AFTERDELETE1
Sélectionnez

CREATE OR REPLACE TRIGGER TRG_AFTERDELETE1
  AFTER DELETE ON EMPLOYEES
  FOR EACH ROW
 
BEGIN
  --Increment the pkg_order.g_order variable
  PKG_ORDER.G_ORDER := PKG_ORDER.G_ORDER + 1;
 
  --Record the row deleted into the AUDIT_EMPLOYEES - table
  INSERT INTO AUDIT_EMPLOYEES(
                              ID
                              ,FIRST_NAME
                              ,LAST_NAME
                              ,EXEC_ORDER
                              ,TRIG_FIRED
                            )
                        VALUES
                            (
                             :OLD.ID
                             ,:OLD.FIRST_NAME
                             ,:OLD.LAST_NAME
                             ,PKG_ORDER.G_ORDER
                             ,'TRG_AFTERDELETE1'
                            );
END TRG_AFTERDELETE1;

Déclencheur TRG_AFTERDELETE2

Création du déclencheur TRG_AFTERDELETE2
Sélectionnez

CREATE OR REPLACE TRIGGER TRG_AFTERDELETE2
  AFTER DELETE ON EMPLOYEES
  FOR EACH ROW
 
BEGIN
  --Increment the pkg_order.g_order variable
  PKG_ORDER.G_ORDER := PKG_ORDER.G_ORDER + 1;
 
  --Record the row deleted into the AUDIT_EMPLOYEES - table
  INSERT INTO AUDIT_EMPLOYEES(
                              ID
                              ,FIRST_NAME
                              ,LAST_NAME
                              ,EXEC_ORDER
                              ,TRIG_FIRED
                            )
                        VALUES
                            (
                             :OLD.ID
                             ,:OLD.FIRST_NAME
                             ,:OLD.LAST_NAME
                             ,PKG_ORDER.G_ORDER
                             ,'TRG_AFTERDELETE2'
                            );
END TRG_AFTERDELETE2;

Déclencheur TRG_AFTERDELETE3

Création du déclencheur TRG_AFTERDELETE3
Sélectionnez

CREATE OR REPLACE TRIGGER TRG_AFTERDELETE3
  AFTER DELETE ON EMPLOYEES
  FOR EACH ROW
 
BEGIN
  --Increment the pkg_order.g_order variable
  PKG_ORDER.G_ORDER := PKG_ORDER.G_ORDER + 1;
 
  --Record the row deleted into the AUDIT_EMPLOYEES - table
  INSERT INTO AUDIT_EMPLOYEES(
                              ID
                              ,FIRST_NAME
                              ,LAST_NAME
                              ,EXEC_ORDER
                              ,TRIG_FIRED
                            )
                        VALUES
                            (
                             :OLD.ID
                             ,:OLD.FIRST_NAME
                             ,:OLD.LAST_NAME
                             ,PKG_ORDER.G_ORDER
                             ,'TRG_AFTERDELETE3'
                            );
END TRG_AFTERDELETE3;

Après création de nos trois déclencheurs, interrogeons la vue USER_OBJECTS pour nous assurer de leur date de création.

 
Sélectionnez

SQL> SELECT OBJECT_NAME, CREATED
  2    FROM USER_OBJECTS
  3   WHERE OBJECT_NAME IN
  4         ('TRG_AFTERDELETE1', 'TRG_AFTERDELETE2', 'TRG_AFTERDELETE3')
  5   ORDER BY CREATED DESC;
 
OBJECT_NAME          CREATED
-------------------- -------------------
TRG_AFTERDELETE3     2009-02-27 13:37:50
TRG_AFTERDELETE2     2009-02-27 13:37:17
TRG_AFTERDELETE1     2009-02-27 12:13:21

Nous allons supprimer quelques lignes de la table EMPLOYEES.
Suppression de la ligne : ( 2 Rachel Carmichael )

 
Sélectionnez

SQL> DELETE FROM EMPLOYEES
  2   WHERE ID = 2;
1 row deleted
 
SQL> COMMIT;
Commit complete

Visualisons le contenu de la table AUDIT_EMPLOYEES
Nous remarquons que la séquence d'exécution de nos trois déclencheurs est : TRG_AFTERDELETE3 avant TRG_AFTERDELETE2 avant TRG_AFTERDELETE1.

 
Sélectionnez

SELECT * FROM AUDIT_EMPLOYEES
/
   ID FIRST_NAME      LAST_NAME       TRIG_FIRED           EXEC_ORDER
----- --------------- --------------- -------------------- ----------
    2 Rachel          Carmichael      TRG_AFTERDELETE3              1
    2 Rachel          Carmichael      TRG_AFTERDELETE2              2
    2 Rachel          Carmichael      TRG_AFTERDELETE1              3

Suppression de la ligne : (3 James Viscusi)

 
Sélectionnez

SQL> DELETE FROM EMPLOYEES
  2   WHERE ID = 3;
1 row deleted
 
SQL> COMMIT;
Commit complete

Visualisons le contenu de la table AUDIT_EMPLOYEES.
Nous remarquons que la séquence d'exécution de nos trois déclencheurs est toujours respectée: TRG_AFTERDELETE3 avant TRG_AFTERDELETE2 avant TRG_AFTERDELETE1.

 
Sélectionnez

SQL> SELECT * FROM AUDIT_EMPLOYEES
/
   ID FIRST_NAME      LAST_NAME       TRIG_FIRED           EXEC_ORDER
----- --------------- --------------- -------------------- ----------
    2 Rachel          Carmichael      TRG_AFTERDELETE3              1
    2 Rachel          Carmichael      TRG_AFTERDELETE2              2
    2 Rachel          Carmichael      TRG_AFTERDELETE1              3
    3 James           Viscusi         TRG_AFTERDELETE3              4
    3 James           Viscusi         TRG_AFTERDELETE2              5
    3 James           Viscusi         TRG_AFTERDELETE1              6

Maintenant, si nous voulons changer l'ordre d'exécution de nos trois déclencheurs de telle sorte que:

TRG_AFTERDELETE2 s'exécute avant TRG_AFTERDELETE3, qui lui va s'exécuter avant TRG_AFTERDELETE1, nous n'aurons d'autre choix que :
Dans un premier temps, supprimer le déclencheur TRG_AFTERDELETE2 puis le créer de nouveau.

Comme ce sera le dernier déclencheur créé, alors il s'exécutera en premier et nous aurons le résultat souhaité: TRG_AFTERDELETE2 avant TRG_AFTERDELETE3 avant TRG_AFTERDELETE1.

Supprimons le déclencheur TRIGGER TRG_AFTERDELETE2

 
Sélectionnez

SQL> DROP TRIGGER TRG_AFTERDELETE2 
  2 /
Trigger dropped

Puis nous recréons le déclencheur TRG_AFTERDELETE2 en exécutant le script "Création du déclencheur TRG_AFTERDELETE2"
Suppression de la ligne : (4 Michael Abbey)

 
Sélectionnez

SQL> DELETE FROM EMPLOYEES
  2   WHERE ID = 4
  3  /
1 row deleted
 
SQL> COMMIT;
Commit complete

Visualisons le contenu de la table AUDIT_EMPLOYEES.

 
Sélectionnez

SQL> SELECT * FROM AUDIT_EMPLOYEES
/
   ID FIRST_NAME      LAST_NAME       TRIG_FIRED           EXEC_ORDER
----- --------------- --------------- -------------------- ----------
    2 Rachel          Carmichael      TRG_AFTERDELETE3              1
    2 Rachel          Carmichael      TRG_AFTERDELETE2              2
    2 Rachel          Carmichael      TRG_AFTERDELETE1              3
    3 James           Viscusi         TRG_AFTERDELETE3              4
    3 James           Viscusi         TRG_AFTERDELETE2              5
    3 James           Viscusi         TRG_AFTERDELETE1              6
    4 Michael         Abbey           TRG_AFTERDELETE2              7
    4 Michael         Abbey           TRG_AFTERDELETE3              8
    4 Michael         Abbey           TRG_AFTERDELETE1              9

Nous remarquons que l'ordre d'exécution de nos déclencheurs a changé:
TRG_AFTERDELETE2 est exécuté avant TRG_AFTERDELETE3 qui est exécuté avant TRG_AFTERDELETE1.

7. Contrôle de l'ordre d'exécution des déclencheurs dans la base de données Oracle 11G à l'aide de la clause FOLLOWS

Oracle 11G a introduit un nouveau concept qui consiste en la clause FOLLOWS qui vous permet de spécifier ou choisir la séquence d'exécution des déclencheurs LMD de même type.
Grâce à cette clause nous pouvons forcer le déclencheur TRG_AFTERDELETE2 à ce qu'il s'exécute avant TRG_AFTERDELETE3 qui lui, va s'exécuter avant TRG_AFTERDELETE1
Nous allons créer nos trois déclencheurs dans cet ordre : AFTERDELETE1 avant AFTERDELETE2 avant AFTERDELETE3.

Les scripts suivants sont exécutés dans la base de données Oracle11G.


Déclencheur TRG_AFTERDELETE1

Création du déclencheur TRG_AFTERDELETE1
Sélectionnez

CREATE OR REPLACE TRIGGER TRG_AFTERDELETE1
  AFTER DELETE ON EMPLOYEES
  FOR EACH ROW
 
BEGIN
  -- Increment pkg_order.g_order variable 
  PKG_ORDER.G_ORDER := PKG_ORDER.G_ORDER + 1;
 
  --Insert the deleted row into the AUDIT_EMPLOYEES table
  INSERT INTO AUDIT_EMPLOYEES
       (ID
        , FIRST_NAME
        , LAST_NAME
        , EXEC_ORDER
        , TRIG_FIRED
        )
      VALUES
        ( :OLD.ID
        , :OLD.FIRST_NAME
        , :OLD.LAST_NAME
        , PKG_ORDER.G_ORDER
        , 'TRG_AFTERDELETE1'
        );
 
END TRG_AFTERDELETE1;

Déclencheur TRG_AFTERDELETE2

Création du déclencheur TRG_AFTERDELETE2
Sélectionnez

CREATE OR REPLACE TRIGGER TRG_AFTERDELETE2
  AFTER DELETE ON EMPLOYEES
  FOR EACH ROW
  FOLLOWS TRG_AFTERDELETE1
 
BEGIN
  -- Increment pkg_order.g_order variable 
  PKG_ORDER.G_ORDER := PKG_ORDER.G_ORDER + 1;
 
  --Insert the deleted row into the AUDIT_EMPLOYEES table
  INSERT INTO AUDIT_EMPLOYEES
       (ID
        , FIRST_NAME
        , LAST_NAME
        , EXEC_ORDER
        , TRIG_FIRED
        )
      VALUES
        ( :OLD.ID
        , :OLD.FIRST_NAME
        , :OLD.LAST_NAME
        , PKG_ORDER.G_ORDER
        , 'TRG_AFTERDELETE2'
        );
 
END TRG_AFTERDELETE2;

Déclencheur TRG_AFTERDELETE3

Création du déclencheur TRG_AFTERDELETE3
Sélectionnez

CREATE OR REPLACE TRIGGER TRG_AFTERDELETE3
  AFTER DELETE ON EMPLOYEES
  FOR EACH ROW
  FOLLOWS TRG_AFTERDELETE2
 
BEGIN
  -- Increment pkg_order.g_order variable 
  PKG_ORDER.G_ORDER := PKG_ORDER.G_ORDER + 1;
 
  --Insert the deleted row into the AUDIT_EMPLOYEES table
  INSERT INTO AUDIT_EMPLOYEES
       (ID
        , FIRST_NAME
        , LAST_NAME
        , EXEC_ORDER
        , TRIG_FIRED
        )
      VALUES
        ( :OLD.ID
        , :OLD.FIRST_NAME
        , :OLD.LAST_NAME
        , PKG_ORDER.G_ORDER
        , 'TRG_AFTERDELETE3'
        );
 
END TRG_AFTERDELETE3;

La clause FOLLOWS spécifiée dans les deux déclencheurs AFTERDELETE2 et AFTERDELETE3 force l'exécution de ces trois déclencheurs dans l'ordre qui suit:
TRG_AFTERDELETE1 avant TRG_AFTERDELETE2 avant TRG_AFTERDELETE3.
Interrogeons la vue USER_OBJECTS

 
Sélectionnez

SQL> SELECT OBJECT_NAME, CREATED
  2    FROM USER_OBJECTS
  3   WHERE OBJECT_NAME IN
  4         ('TRG_AFTERDELETE1', 'TRG_AFTERDELETE2','TRG_AFTERDELETE3')
  5   ORDER BY CREATED DESC;
 
OBJECT_NAME          CREATED
-------------------- --------------------
TRG_AFTERDELETE3     2009-03-01 12:30:46
TRG_AFTERDELETE2     2009-03-01 12:29:34
TRG_AFTERDELETE1     2009-03-01 12:26:24

Notons que nos trois déclencheurs sont créés dans cet ordre TRG_AFTERDELETE1 avant TRG_AFTERDELETE2 avant TRG_AFTERDELETE3.
Visualisons le contenu de la table EMPLOYEES

 
Sélectionnez

SQL>  SELECT *
  2   FROM EMPLOYEES
  3   WHERE ID < 6;
 
    ID FIRST_NAME      LAST_NAME
------ --------------- ---------------
     1 Marlene         Theriault
     2 Rachel          Carmichael
     3 James           Viscusi
     4 Michael         Abbey
     5 Michael         Corey

Nous allons supprimer quelques lignes de la table EMPLOYEES.
Suppression de la ligne : ( 1 Marlene Theriault )

 
Sélectionnez

SQL>  DELETE FROM employees
  2   WHERE ID = 1;
1 row deleted
 
SQL>  COMMIT;
Commit complete 

Visualisons le contenu de la table AUDIT_EMPLOYEES.

 
Sélectionnez

SQL>  SELECT *
  2   FROM AUDIT_EMPLOYEES;
 
    ID FIRST_NAME      LAST_NAME       TRIG_FIRED           EXEC_ORDER
------ --------------- --------------- -------------------- ----------
     1 Marlene         Theriault       TRG_AFTERDELETE1              1
     1 Marlene         Theriault       TRG_AFTERDELETE2              2
     1 Marlene         Theriault       TRG_AFTERDELETE3              3

Suppression de la ligne : ( 2 Rachel Carmichael )

 
Sélectionnez

SQL>  DELETE FROM employees
  2   WHERE ID = 2;
1 row deleted
 
SQL>  COMMIT;
Commit complete 

Visualisons le contenu de la table AUDIT_EMPLOYEES.

 
Sélectionnez

SQL>  SELECT *
  2   FROM AUDIT_EMPLOYEES;
 
    ID FIRST_NAME      LAST_NAME       TRIG_FIRED           EXEC_ORDER
------ --------------- --------------- -------------------- ----------
     1 Marlene         Theriault       TRG_AFTERDELETE1              1
     1 Marlene         Theriault       TRG_AFTERDELETE2              2
     1 Marlene         Theriault       TRG_AFTERDELETE3              3
     2 Rachel          Carmichael      TRG_AFTERDELETE1              4
     2 Rachel          Carmichael      TRG_AFTERDELETE2              5
     2 Rachel          Carmichael      TRG_AFTERDELETE3              6

Quand nous supprimons des lignes à partir de la table EMPLOYEES, les trois déclencheurs s'exécutent toujours dans le même ordre, soit :

TRG_AFTERDELETE1 avant TRG_AFTERDELETE2 avant TRG_AFTERDELETE3 et ceci, même si le déclencheur TRG_AFTERDELETE3 est le plus récemment créé.

Quand une opération de suppression survient sur la table EMPLOYEES, celle-ci entrainera toujours l'exécution du TRG_AFTERDELETE3 après celle de TRG_AFTERDELETE2 qui s'exécutera après TRG_AFTERDELETE1 et ceci quelque soit leur date de création.

Plus de détails concernant les déclencheurs composés Documentation Oracle

8. Remerciements

Je remercie Monsieur Vincent Rogier et toute l'équipe Oracle de developpez.com pour la lecture de la première version de ce tutoriel.
Je remercie particulièrement Monsieur Guillaume Rossolini, Rédacteur-en-Chef pour m'avoir donné cette opportunité et offert un espace personnel sur Developpez.com

9. Références

PL/SQL Programming by Michael Mclaughlin.
Oracle Database 11g: New Features by Robert Freeman.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+