Pb Tables Mutantes sur Trigger et Autonomous_Transaction

[Gumpy]

Bonjour,

J'ai des soucis avec un problème de table mutante, naturellement j'ai fait des recherches sur google et j'ai lu attentivement le tutoriel http://sgbd.developpez.com/oracle/ora-04091/

J'ai simplifié mon problème en ce que je vous expose ci-après pour faciliter la compréhension, mais il s'agît également de ce que je suis en train de tester.

Mes Tables :

etudiant (id, nom, moyenne);
matiere (id, note, id_etudiant);

Je souhaiterai calculer la moyenne d'un étudiant à chaque fois qu'une note est modifiée (imaginons que toutes les notes dont j'ai besoin sont déjà insérées et initialisées). Pour cela on m'impose l'utilisation d'un Trigger.

Mon Trigger sera appelé lors de l'utilisation d'un UPDATE de cette forme :

Code :

1
2
 
UPDATE matiere SET note = 20 WHERE id = xx;

xx correspondant à l'id en base de la note modifiée.

Voici le Trigger :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
CREATE OR REPLACE
TRIGGER TEST_TG
  AFTER UPDATE ON matiere 
   REFERENCING NEW AS n
 FOR EACH ROW
 
DECLARE
  idE integer;
  moyenne_generale number;
BEGIN
 
SELECT :n.id_etudiant INTO idE FROM dual;
 
        SELECT AVG(note) INTO moyenne_generale
          FROM matiere
          WHERE id_etudiant = idE;
 
      UPDATE etudiant
        SET moyenne = moyenne_generale
        WHERE id = idE;
 
END;

C'est donc là qu'apparait mon problème de table mutante, faisant un SELECT sur une table au moment où elle est modifiée.

1ere remarque : Dans le tuto Développez.com, la méthode "E-4. Je suis grand, c'est moi qui gère" me fait sortir du Trigger sans modifier la moyenne.

2eme remarque : Je préfère ne pas avoir recours aux autres méthodes de ce tutoriel (pour le moment...) pour diverses contraintes auxquelles je suis soumis.

J'ai eu accès à un code semblable au mien qui marche en utilisant une directive PRAGMA AUTONOMOUS_TRANSACTION, de plus après des recherches cette solution semble effectivement marcher, et elle m'intéresse très fortement.

J'ai donc modifié mon Trigger comme suit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE OR REPLACE
TRIGGER TEST_TG
  AFTER UPDATE ON matiere 
   REFERENCING NEW AS n
 FOR EACH ROW
 
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  idE integer;
  moyenne_generale number;
BEGIN
 
SELECT :n.id_etudiant INTO idE FROM dual;
 
        SELECT AVG(note) INTO moyenne_generale
          FROM matiere
          WHERE id_etudiant = idE;
 
      UPDATE etudiant
        SET moyenne = moyenne_generale
        WHERE id = idE;
COMMIT;
END;

Et là arrive ma véritable question : ce trigger a les mêmes effets qu'un BEFORE UPDATE, c'est à dire que la moyenne calculée est celle qui était vraie avant la modification d'une des notes.
J'ai essayé de déplacer le COMMIT dans le trigger, et de le mettre partout dans le trigger (), même résultat.

J'aimerais donc savoir si il y avait une explication à ce phénomène et si je pouvais y remédier (en gardant la solution des transactions autonomes qui semble marcher dans le même cas de figure sur d'autres applications...).

Maintenant si vous me déconseillez cette méthode (même si jamais j'arrivai à la faire marcher), j'aimerai également savoir pourquoi (sachant qu'il s'agît d'une application multi-thread)

Merci d'avance pour vos conseils

[mnitu]

Chaque fois quand j'arrive à faire PRAGMA_AUTONOMOUS_TRANSACTION et COMMIT dans un trigger je me dit que je suis bien dans les choux et donc je change de solution.

[plaineR]

Une des solutions envisageables est de mémoriser les matières mises à jour (tableau, table temporaire, ...) dans un trigger for each row

Puis dans un trigger after update global, de calculer les moyennes pour ces matières et de mettre à jour la table etudiant.

Si tu peux passer par une table temporaire, il me semble que c'est la meilleure solution car elle sera automatiquement remise à vide au commit. Le tableau devra être lui remis à zéro.

[orafrance]

une vue matérialisée en ajoutant la colonne moyenne qui se rafraichie ON_COMMIT me parait beaucoup mieux indiquer qu'un trigger.

Eventuellement, tu peux peut-être créer une fonction en AUTONOMOUS TRANSACTION qui retourne la moyenne d'un éléve que tu appelleras dans ton trigger.

[plaineR]

Envoyé par orafrance

Eventuellement, tu peux peut-être créer une fonction en AUTONOMOUS TRANSACTION qui retourne la moyenne d'un éléve que tu appelleras dans ton trigger.

Non cela ne marchera pas puisque avec ta fonction en autonomous_transaction tu verras la valeur de la note avant la mise à jour.

[orafrance]

tu peux passer old et new en paramètre pour remplacer la valeur dans la fonction éventuellement

[Gumpy]

Merci pour tous vos conseils! Je vais essayer tout ça