I. Rappels / définitions▲
- Une instruction DML est un SELECT, un INSERT, un UPDATE ou un DELETE (plus quelques autres qui ne sont pas pertinents ici).
- Un déclencheur de niveau ligne, caractérisé par la clause FOR EACH ROW, s'exécute autant de fois qu'il y a de lignes touchées par l'instruction DML à laquelle répond ce déclencheur.
- Un déclencheur de niveau instruction ne s'exécute qu'une fois, même si l'instruction DML touche plusieurs lignes à la fois.
- Dans un déclencheur de niveau ligne, les variables de référence OLD et NEW permettent de faire référence aux champs de la ligne en cours de modification, OLD désignant les valeurs avant modification, et NEW les valeurs après modification.
- Un accès direct à une table a lieu lorsque l'instruction en cours contient explicitement le nom de cette table. Il existe aussi des accès indirects : soit par le biais d'un appel de fonction ou de procédure qui encapsule l'accès à cette table, soit au niveau système lors de la vérification des contraintes référentielles ou de l'exécution des déclencheurs.
- Une table mutante est simplement une table qui est en cours de modification du fait d'une instruction DML INSERT, UPDATE ou DELETE, ou qui pourrait être modifiée en raison d'une contrainte DELETE CASCADE. Soit par exemple une table A et une table B, B possédant une clé étrangère assortie d'une clause DELETE CASCADE pointant sur A. Lorsqu'une suppression est faite sur A, A et B sont considérées comme mutantes.
- Une table contraignante est une table qui doit être lue, même de manière implicite par le système, pour vérifier les contraintes référentielles. Soit par exemple une table A et une table B, B possédant une clé étrangère pointant sur A. En cas de suppression dans A, B est contraignante, car le système doit vérifier qu'aucun enregistrement de B ne faisait référence à A. De même, en cas d'insertion dans B, A est contraignante car le système doit vérifier qu'il existe dans A une clé primaire égale à la nouvelle valeur placée dans B.
- Une variable déclarée dans un paquetage, mais en dehors de toute fonction ou procédure, constitue une variable globale au sein de la session utilisateur.
- Une instruction unitaire est une instruction SQL qui est considérée comme indécomposable par Oracle, même si elle touche plusieurs lignes à la fois. Un UPDATE qui touche 10 lignes est une instruction unitaire. Oracle garantit le résultat d'une instruction unitaire, mais se réserve le choix du cheminement pour y parvenir. Ce cheminement peut même varier d'une exécution à l'autre.
- Un déclencheur INSTEAD OF, introduit avec Oracle 8i, s'exécute en lieu et place de l'instruction DML à laquelle il répond. Ce type de déclencheur ne peut être placé que sur une vue. Les déclencheurs INSTEAD OF sont obligatoirement de niveau ligne, même si la clause FOR EACH ROW n'est pas mentionnée.
- Oracle 8i a introduit la notion de table temporaire. Une table de ce type est aussi définitive qu'une table classique, mais présente les caractéristiques suivantes : elle peut être utilisée par plusieurs utilisateurs, mais chacun n'y voit que les données qu'il a lui-même introduites. Les données qu'on y met disparaissent soit à la fin de la session, soit à la fin de la transaction suivant l'option choisie.
II. Dans quelles circonstances l'erreur ORA-04091 se produit-elle ?▲
L'erreur ORA-04091 se produit dans chacun des cas suivants.
- Si un déclencheur de niveau ligne (qu'il soit BEFORE ou AFTER) tente d'accéder, même par un SELECT, à une table mutante.
- Si un déclencheur de niveau instruction résultant d'une contrainte DELETE CASCADE tente d'accéder, même par un SELECT, à une table mutante.
- Jusqu'en version Oracle 8.0.x, lire ou modifier par un déclencheur une clé primaire, unique ou étrangère d'une table contraignante était interdit et provoquait l'erreur ORA-04094. La notion de table contraignante a disparu depuis Oracle 8i (ce qui n'est pas sans danger dans certains cas).
En revanche, il n'y a pas d'erreur ORA-04091 dans les cas suivants.
- L'instruction DML déclenchante est un INSERT INTO … VALUES(…) avec valeurs littérales « en dur » (donc forcément une seule ligne insérée, contrairement à un INSERT/SELECT qui pourrait traiter plusieurs lignes d'un coup)
- Le déclencheur est de niveau instruction (AFTER ou BEFORE) et son exécution n'est pas due à une contrainte DELETE CASCADE.
- Le déclencheur est de type INSTEAD OF.
III. Qu'est-ce qui justifie ce comportement ?▲
Selon la documentation Oracle, ces contraintes s'imposent pour respecter le principe de lecture cohérente. La lecture cohérente s'applique à deux niveaux.
- Entre deux sessions utilisateurs, elle garantit que les modifications faites par un utilisateur ne seront visibles aux autres utilisateurs qu'après validation de la transaction du premier. Tant que cette transaction n'est pas validée, c'est l'état préalable des données, dit « image avant », qui est visible aux autres utilisateurs.
- Au sein d'une même transaction, et indépendamment du fait que la transaction soit validée ou non finalement, la lecture cohérente s'applique pour chaque instruction unitaire. Elle garantit alors qu'on ne puisse pas accéder aux données tant que l'instruction unitaire n'est pas terminée. C'est pourtant précisément ce qu'on tente de faire lorsqu'un déclencheur de niveau ligne, qu'il soit BEFORE ou AFTER, veut accéder à la table sur laquelle il est défini. D'où l'erreur ORA-04091 !
IV. Peut-on se permettre d'ignorer l'erreur ORA-04091 ?▲
La lecture cohérente au niveau des instructions unitaires est une véritable sécurité d'Oracle, et pas uniquement une contrainte pénible.
L'exemple fictif suivant, à défaut d'utilité pratique, devrait illustrer la chose.
Supposons qu'on veuille gérer manuellement, par le biais d'un déclencheur, une contrainte d'unicité sur une colonne numérique qui contient initialement les valeurs 1, 2, 3, 4, 5.
CREATE TABLE TEST(COL1 INTEGER );Pour assurer l'unicité, on crée un déclencheur AFTER INSERT OR UPDATE FOR EACH ROW, lequel va vérifier que la nouvelle valeur n'existe pas déjà dans la table.
Puis on exécute l'instruction suivante pour incrémenter COL1 de 1 dans toute la table :
UPDATE TEST SET COL1=COL1+1;Pour la première ligne, l'incrémentation a lieu (1+1), puis le déclencheur vérifie si 2 n'existe pas déjà. Il conclut que oui, en conséquence de quoi on va annuler la transaction.
Pourtant, si la vérification s'effectuait après le traitement de toutes les lignes, on aurait fort logiquement 2, 3, 4, 5, 6, valeurs pour lesquelles la contrainte d'unicité est respectée.
L'utilité de la lecture cohérente de niveau instruction unitaire, c'est justement de ne pas donner accès aux états transitoires des données, qui ne permettent de tirer aucune conclusion valable. L'erreur ORA-04091 est donc un véritable signal d'alarme préventif.
Il est à noter qu'Oracle ne garantit absolument pas l'ordre dans lequel seront traitées les lignes au sein d'une instruction unitaire, cet ordre pouvant notamment dépendre des options d'optimisation en vigueur.
V. Comment contourner l'erreur ORA-04091 ?▲
Plusieurs pistes sont envisageables et plus ou moins praticables ou recommandables suivant le contexte. En voici 4, appliquées à la situation suivante :
On gère des voyages avec un nombre de places limité, et on doit évidemment vérifier qu'il reste des places disponibles avant d'accepter une nouvelle inscription. On utilise les tables CLIENT, VOYAGE, et INSCRIPTION. On utilise initialement un déclencheur BEFORE INSERT FOR EACH ROW sur la table INSCRIPTION pour vérifier qu'il y a encore des places, et on est confronté à l'erreur ORA-04091, car cette table est considérée comme mutante.
CREATE TABLE CLIENT(
IDC INTEGER PRIMARY KEY ,
NOM VARCHAR2 (40));
CREATE TABLE VOYAGE(
IDV INTEGER PRIMARY KEY ,
DESTINATION VARCHAR2 (40),
MAXPLACE INTEGER ) -- nombre total de places
;
CREATE TABLE INSCRIPTION(
IDC INTEGER REFERENCES CLIENT(IDC),
IDV INTEGER REFERENCES VOYAGE(IDV),
DATERESERV DATE ,
CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 25);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
COMMIT ;
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW
DECLARE
NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
NB_MAXPLACE INTEGER ; -- nombre de places total
BEGIN
SELECT COUNT (*) INTO NB_RESERVE FROM INSCRIPTION
WHERE IDV=:NEW.IDV;
SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE
WHERE IDV=:NEW.IDV;
IF NB_MAXPLACE - NB_RESERVE < 0 THEN
DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet');
END IF ;
END ;
/
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
ERREUR à la ligne 1 :
ORA-04091: table INSCRIPTION en mutation, déclencheur/fonction ne peut la voir
ORA-06512: à "TRIG_INSCRIPTION", ligne 5
ORA-04088: erreur lors d'exécution du déclencheur 'TRIG_INSCRIPTION'V-A. Modification de la structure de données▲
Ce qui pose problème dans ce déclencheur, c'est qu'il va interroger la table INSCRIPTION, sur laquelle il est défini, pour dénombrer les inscriptions déjà effectuées. Il interroge donc une table mutante. Si on arrive à éviter d'interroger cette table au sein du déclencheur, on aura résolu le problème.
Dans la table VOYAGE, on a un champ MAXPLACE qui indique le nombre de places total pour le voyage en question. Pourquoi ne pas ajouter un champ dénormalisé PLACEDISPO, qui indiquera dynamiquement combien de places il reste ?
CREATE TABLE CLIENT(
IDC INTEGER PRIMARY KEY ,
NOM VARCHAR2 (40));
CREATE TABLE VOYAGE(
IDV INTEGER PRIMARY KEY ,
DESTINATION VARCHAR2 (40),
MAXPLACE INTEGER , -- nombre total de places
PLACEDISPO INTEGER ) -- nombre de places disponibles
;
CREATE TABLE INSCRIPTION(
IDC INTEGER REFERENCES CLIENT(IDC),
IDV INTEGER REFERENCES VOYAGE(IDV),
DATERESERV DATE ,
CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE, PLACEDISPO) VALUES (10, 'VENISE', 25, 2);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE, PLACEDISPO) VALUES (11, 'PRAGUE', 20, 0);
COMMIT ;
-- déclencheur qui n'interroge plus la table mutante
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW
DECLARE
NB_DISPO INTEGER ;
BEGIN
SELECT PLACEDISPO INTO NB_DISPO FROM VOYAGE
WHERE IDV=:NEW.IDV;
IF NB_DISPO < 1 THEN
DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet');
ELSE
UPDATE VOYAGE SET PLACEDISPO=PLACEDISPO - 1
WHERE IDV=:NEW.IDV;
END IF ;
END ;
/
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
1 ligne créée.
-- DUBOIS aimerait bien aller à Prague :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 2, 11, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
Désolé, voyage completCette modification de la structure de données a donc permis de ne plus interroger la table mutante au sein du déclencheur, si bien que l'erreur ORA-04091 ne se produit plus.
V-B. Méthode des deux déclencheurs (plusieurs variantes)▲
On a vu plus haut que les déclencheurs de niveau instruction ne sont pas sujets à l'erreur ORA-04091. L'idée est donc de reporter dans un déclencheur AFTER de niveau instruction les actions qui avaient provoqué l'erreur dans le déclencheur de niveau ligne. Mais il faut que ce second déclencheur soit en mesure d'identifier les enregistrements traités par le premier, pour leur appliquer, après coup, les vérifications utiles. On a donc besoin d'un espace de communication entre les deux déclencheurs.
Cet espace de communication peut être réalisé de différentes manières : par une table temporaire ou par des variables globales déclarées dans un paquetage. L'option de la table temporaire est sans doute la plus simple, mais n'est praticable qu'à partir d'Oracle 8i.
Suivant la nature de l'instruction DML sous-jacente au déclencheur, il peut être suffisant de mémoriser uniquement la clé primaire ou le ROWID des enregistrements (pour un INSERT), ou on peut à l'inverse, dans le cas d'un UPDATE et d'un DELETE, avoir besoin des anciennes valeurs pour être en mesure de les rétablir sélectivement, sans pour autant annuler la transaction complète.
Méthode des deux déclencheurs avec table temporaire
CREATE TABLE CLIENT(
IDC INTEGER PRIMARY KEY ,
NOM VARCHAR2 (40));
CREATE TABLE VOYAGE(
IDV INTEGER PRIMARY KEY ,
DESTINATION VARCHAR2 (40),
MAXPLACE INTEGER ) -- nombre total de places
;
CREATE TABLE INSCRIPTION(
IDC INTEGER REFERENCES CLIENT(IDC),
IDV INTEGER REFERENCES VOYAGE(IDV),
DATERESERV DATE ,
CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 25);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
COMMIT ;
-- Création d'une table temporaire vide de même structure que INSCRIPTION
CREATE GLOBAL TEMPORARY TABLE TEMP_INSCRIPTION AS SELECT * FROM INSCRIPTION WHERE 0=1;
-- 1er déclencheur, de niveau ligne, qui n'interroge plus la table mutante
-- à la place, il stocke dans la table temporaire les données insérées
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW
BEGIN
INSERT INTO TEMP_INSCRIPTION(IDC, IDV, DATERESERV) VALUES (:NEW.IDC, :NEW.IDV, :NEW.DATERESERV);
END ;
/
-- second déclencheur, de niveau instruction, qui vérifie qu'il y a des places libres
-- il s'exécute une seule fois, après le traitement de tous les enregistrements touchés par l'INSERT sous-jacent
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION2 AFTER INSERT ON INSCRIPTION
DECLARE
NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
NB_MAXPLACE INTEGER ; -- nombre de places total
BEGIN
FOR LIGNE IN (SELECT * FROM TEMP_INSCRIPTION ORDER BY DATERESERV) LOOP
SELECT COUNT (*) INTO NB_RESERVE FROM INSCRIPTION
WHERE IDV=LIGNE.IDV;
SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE
WHERE IDV=LIGNE.IDV;
IF NB_MAXPLACE - NB_RESERVE < 0 THEN
DBMS_OUTPUT.PUT_LINE('Réservation impossible pour voyage ' || LIGNE.IDV || ' et client ' || LIGNE.IDC);
-- on supprime les inscriptions excédentaires
DELETE FROM INSCRIPTION WHERE IDV=LIGNE.IDV AND IDC=LIGNE.IDC;
END IF ;
END LOOP ;
-- tout à la fin, on remet à zéro la table temporaire. À noter qu'un TRUNCATE n'est pas possible ici, car il commencerait une nouvelle transaction
DELETE FROM TEMP_INSCRIPTION;
END ;
/
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
1 ligne créée.Méthode des deux déclencheurs avec variables globales
CREATE TABLE CLIENT(
IDC INTEGER PRIMARY KEY ,
NOM VARCHAR2 (40));
CREATE TABLE VOYAGE(
IDV INTEGER PRIMARY KEY ,
DESTINATION VARCHAR2 (40),
MAXPLACE INTEGER ) -- nombre total de places
;
CREATE TABLE INSCRIPTION(
IDC INTEGER REFERENCES CLIENT(IDC),
IDV INTEGER REFERENCES VOYAGE(IDV),
DATERESERV DATE ,
CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 25);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
COMMIT ;
-- création dans un paquetage d'une variable globale de type table indicée, dans laquelle le déclencheur de niveau ligne va mémoriser les données insérées. Les tables indicées requièrent la déclaration préalable d'un type équivalent. On utilise également une variable compteur qu'on initialise à 0.
CREATE OR REPLACE PACKAGE PKG_INSERT AS
TYPE T_INSCRIPTION IS TABLE OF INSCRIPTION%ROWTYPE INDEX BY BINARY_INTEGER;
V_INSCRIPTION T_INSCRIPTION;
V_COMPTEUR INTEGER :=0;
END ;
/
-- 1er déclencheur, de niveau ligne, qui n'interroge plus la table mutante
-- à la place, il stocke dans une variable globale les données insérées
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW
BEGIN
PKG_INSERT.V_COMPTEUR:= PKG_INSERT.V_COMPTEUR + 1;
PKG_INSERT.V_INSCRIPTION(PKG_INSERT.V_COMPTEUR).IDC := :NEW.IDC;
PKG_INSERT.V_INSCRIPTION(PKG_INSERT.V_COMPTEUR).IDV := :NEW.IDV;
PKG_INSERT.V_INSCRIPTION(PKG_INSERT.V_COMPTEUR).DATERESERV := :NEW.DATERESERV;
END ;
/
-- second déclencheur, de niveau instruction, qui vérifie qu'il y a des places libres
-- il s'exécute une seule fois, après le traitement de tous les enregistrements touchés par l'INSERT sous-jacent
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION2 AFTER INSERT ON INSCRIPTION
DECLARE
NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
NB_MAXPLACE INTEGER ; -- nombre de places total
BEGIN
FOR LIGNE IN 1.. PKG_INSERT.V_INSCRIPTION.COUNT LOOP
SELECT COUNT (*) INTO NB_RESERVE FROM INSCRIPTION
WHERE IDV=PKG_INSERT.V_INSCRIPTION(LIGNE).IDV;
SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE
WHERE IDV=PKG_INSERT.V_INSCRIPTION(LIGNE).IDV;
IF NB_MAXPLACE - NB_RESERVE < 0 THEN
DBMS_OUTPUT.PUT_LINE('Réservation impossible pour voyage ' || PKG_INSERT.V_INSCRIPTION(LIGNE).IDV ||
' et client ' || PKG_INSERT.V_INSCRIPTION(LIGNE).IDC);
-- on supprime les inscriptions excédentaires
DELETE FROM INSCRIPTION WHERE IDV=PKG_INSERT.V_INSCRIPTION(LIGNE).IDV
AND IDC=PKG_INSERT.V_INSCRIPTION(LIGNE).IDC;
END IF ;
END LOOP ; -- tout à la fin, on remet à zéro le compteur.
PKG_INSERT.V_COMPTEUR:=0;
END ;Le 3e déclencheur
Certains auteurs particulièrement attentifs à la robustesse du code recommandent d'utiliser un 3e déclencheur de niveau instruction et de type BEFORE, dans lequel on réinitialise les variables globales avant chaque utilisation.
En effet, si une erreur non gérée survenait avant la fin du deuxième déclencheur qui réinitialise le compteur, ce compteur conserverait une valeur erronée, car l'annulation de la transaction n'a aucun effet sur les variables globales.
-- modification du paquetage : V_VIDE est un tableau qui reste toujours vide
CREATE OR REPLACE PACKAGE PKG_INSERT AS
TYPE T_INSCRIPTION IS TABLE OF INSCRIPTION%ROWTYPE INDEX BY BINARY_INTEGER;
V_INSCRIPTION T_INSCRIPTION;
V_VIDE T_INSCRIPTION;
V_COMPTEUR INTEGER :=0;
END ;
/
-- création du déclencheur supplémentaire qui réinitialise les variables globales
CREATE OR REPLACE TRIGGER TRG_INSCRIPTION3 BEFORE INSERT ON INSCRIPTION
BEGIN
PKG_INSERT.V_COMPTEUR:=0;
PKG_INSERT.V_INSCRIPTION:= PKG_INSERT.V_VIDE;
END ;
/V-C. Utilisation d'un déclencheur INSTEAD OF▲
Dans certains cas, l'utilisation de déclencheurs de type INSTEAD OF peut être adaptée.
Cette solution est à utiliser prudemment, dans la mesure où les déclencheurs INSTEAD OF ne déclenchent pas le signal d'alarme ORA-04091, ce qui revient à travailler sans filet. De ce fait, une incohérence dans la lecture consistante peut parfaitement passer inaperçue, avec des conséquences évidemment fâcheuses.
Par ailleurs, si quelqu'un attaque directement la table sous-jacente à la vue, le déclencheur INSTEAD OF ne se déclenchera pas. Cette solution est donc réservée à un environnement extrêmement contrôlé.
Attention ! Le code suivant est destiné à illustrer le principe et la syntaxe d'un déclencheur INSTEAD OF . Fonctionnellement, il n'est pas une solution adaptée à notre exemple de la gestion de voyages. En effet, en cas d'insertion multiligne, le corps du déclencheur ne voit que l'état des données avant l'insertion, si bien que le count(*) renverra toujours la même valeur, sans tenir compte de l'insertion des enregistrements précédents. CREATE TABLE CLIENT(
IDC INTEGER PRIMARY KEY ,
NOM VARCHAR2 (40));
CREATE TABLE VOYAGE(
IDV INTEGER PRIMARY KEY ,
DESTINATION VARCHAR2 (40),
MAXPLACE INTEGER ) -- nombre total de places
;
CREATE TABLE INSCRIPTION(
IDC INTEGER REFERENCES CLIENT(IDC),
IDV INTEGER REFERENCES VOYAGE(IDV),
DATERESERV DATE ,
CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 25);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
COMMIT ;
-- Création d'une vue sur la table INSCRIPTION pour le support des déclencheurs INSTEAD OF
CREATE OR REPLACE VIEW V_INSCRIPTION AS SELECT * FROM INSCRIPTION;
CREATE OR REPLACE TRIGGER TRIG_V_INSCRIPTION INSTEAD OF INSERT ON V_INSCRIPTION FOR EACH ROW
DECLARE
NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
NB_MAXPLACE INTEGER ; -- nombre de places total
BEGIN
SELECT COUNT (*) INTO NB_RESERVE FROM V_INSCRIPTION
WHERE IDC=:NEW.IDC
AND IDV=:NEW.IDV;
SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE
WHERE IDV=:NEW.IDV;
IF NB_MAXPLACE - NB_RESERVE < 1 THEN
DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet');
ELSE
-- dans un déclencheur INSTEAD OF, l'instruction DML sous-jacente ne s'exécute pas. On traite donc l'insertion manuellement
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) VALUES (:NEW.IDC, :NEW.IDV, :NEW.DATERESERV);
END IF ;
END ;
/
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
1 ligne créée.V-D. Je suis grand, c'est moi qui gère▲
Comme il a été dit, l'erreur ORA-04091 est un signal d'alarme destiné à attirer l'attention sur un risque potentiel d'incohérence. Si l'on est absolument certain que ce signal d'alarme n'est pas pertinent, on peut envisager de l'ignorer, par le biais d'une interception d'exception dans laquelle on ne fait rien de concret.
CREATE TABLE CLIENT(
IDC INTEGER PRIMARY KEY ,
NOM VARCHAR2 (40));
CREATE TABLE VOYAGE(
IDV INTEGER PRIMARY KEY ,
DESTINATION VARCHAR2 (40),
MAXPLACE INTEGER ) -- nombre total de places
;
CREATE TABLE INSCRIPTION(
IDC INTEGER REFERENCES CLIENT(IDC),
IDV INTEGER REFERENCES VOYAGE(IDV),
DATERESERV DATE ,
CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 25);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
COMMIT ;
-- on introduit une gestion d'exception
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW
DECLARE
NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
NB_MAXPLACE INTEGER ; -- nombre de places total
TABLE_MUTANTE EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_MUTANTE, -4091);
BEGIN
SELECT COUNT (*) INTO NB_RESERVE FROM INSCRIPTION
WHERE IDV=:NEW.IDV;
SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE
WHERE IDV=:NEW.IDV;
IF NB_MAXPLACE - NB_RESERVE < 0 THEN
DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet');
END IF ;
EXCEPTION
WHEN TABLE_MUTANTE THEN
DBMS_OUTPUT.PUT_LINE('Fausse alerte');
END ;
/
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
1 ligne créée.VI. Sources principales (sans ordre particulier)▲
- http://www.akadia.com/services/ora_mutating_table_problems.html
- http://asktom.oracle.com/ (plusieurs articles)
- http://www.dulcian.com/papers/INSTEAD OF Trigger Views_ODTUG.htm
- « Oracle 8i : programmation avancée PL/SQL » de Scott URMAN