Discussion :

[Drizzt [Drone38]]

Bonjour,

Tout d'abord je précise que j'ai lu l'article suivant:
http://sgbd.developpez.com/oracle/ora-04091/

Maintenant j'expose mon problème:

J'ai une table T. Je souhaite ajouter un trigger conditionnel qui lors d'insert or update , sous certaines conditions, effectue le traitement suivant:

• Lecture dans la table T --> Récupération d'une valeur depuis une autre ligne de T
• Ecriture dans la table T --> Ajout d'une nouvelle ligne.

En effectuant le trigger de façon naturelle, une erreur ora-04091 est soulevée au niveau du select et au niveau de l'insert.
Voila le code d'un tel trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE TRIGGER T_TRIG
AFTER INSERT OR UPDATE ON T
REFERENCING NEW AS n  
FOR EACH ROW 
WHEN <condition>
BEGIN
 
SELECT c3 INTO v_x 
FROM T
WHERE T.c1 = value1
  AND T.c2= (SELECT MAX(c2) FROM T
             WHERE c1 = value1
             );
 
INSERT INTO T(c1,c2,c3,flag)
VALUES (:n.c1, :n.c2, v_x+ :n.c3, 1);
 
EXCEPTION     
  WHEN OTHERS THEN
       RAISE;
END ;
/

Première solution testée (en fait je l'ai pas testée en premier mais passons)
La detection d'erreur.
Le problème est que si cela semble pouvoir fonctionner au niveau du select, je n'arrive pas à effectuer l'insert.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
BEGIN
INSERT INTO T(c1,c2,c3,flag)
VALUES (:n.c1, :n.c2, v_x+ :n.c3, 1);
EXCEPTION 
WHEN TABLE_MUTANTE THEN 
  null;
END;

Ce bout de code m'evite d'obtenir une erreur, mais j'ai pas mon insert d'effectué (ce qui est normal).

Seconde solution, utilisation d'une table temporaire tel qu'il est decrit dans l'article mentionné plus haut.
Donc je remplace mon trigger par un premier qui remplit une table temporaire, puis un second qui est semblable à celui cité plus haut, mais sans le FOR EACH ROW et qui boucle sur les entrées dans la table temporaire.

Premier problème : on ne peut plus mettre de WHEN ce qui m'empeche d'utiliser le flag pour eviter d'appeler le curseur sur l'insertion faite par le trigger. Problème facilement contournable par un test en début de trigger.

Second problème : cette solution fonctionne, sauf que je ne peux l'appliquer sur ma table. La table T est en effet volumineuse et sujette à un traitement non modifiable, faisant appel massivement à des insert et update sur cette table. Du coup je doit absolument mettre un WHEN dans mon trigger pour eviter de le déclancher (après tests, le déclanchement d'un trigger meme vide entraine une augmentation massive du temps de traitement que la clause WHEN permet d'eviter). Ce que je ne peux pas avec le trigger de niveau instruction.

Troisieme solution, non testée, utilisation de vue avec un trigger INSTEAD OF. Je pense que cette solution risque de poser problème au niveau des performances vu la volumetrie de la table (plusieurs millions d'entrées). Et je ne suis même pas sur que cette solution soit applicable à mon problème.
edit> On ne peut pas utiliser de WHEN non plus dans ce cas, donc solution impossible.



Bref, je suis dans l'impasse, si quelqu'un avait une idée autre, voir n'utilisant pas de trigger, en sachant qu'on ne peut transferer le traitement partout ou il y a des insert et update.
De plus si quelqu'un à une opinion sur la faisabilité de la solution 3 dans mon cas, je suis preneur aussi. (Je ne peux hélas pas tester cette solution en situation réelle, avec la volumetrie complète).


Merci d'avance.

[orafrance]

recherche autonomous_transaction

[PpPool]

Bonjour

une solution à explorer :

créer une procédure en déclarant PRAGMA AUTONOMOUS_TRANSACTION;

Un exemple que j'ai utilisé dans un cas similaire au tien
Tu pourras je pense t'en inspirer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
PROCEDURE DEL_ECHECS(myIP IN VERROU.VERR_IP %TYPE)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM ECHEC WHERE ECHE_IP= :1 AND ECHE_TIMESTAMP<SYSDATE- 5/(24*60) ' USING myIP;
  COMMIT;
END DEL_ECHECS;

ensuite j'ai juste à appeler ma procédure DEL_ECHECS dans mon TRIGGER
Dans mon cas c'était BEFORE INSERT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE OR REPLACE TRIGGER TRIG_DEL_ECHEC
BEFORE INSERT ON ECHEC
FOR EACH ROW
BEGIN
  BIBLIO_WEB.DEL_ECHECS( :NEW.ECHE_IP );
END;

Excuses moi si je n'adapes pas à ton code, je n'ai pas le temps
et puis cela te seras certainement plus utile de le faire par toi meme

@

[PpPool]

recherche autonomous_transaction

me suis encore fait grillé

[Drizzt [Drone38]]

Merci à vous deux, je vais tester ça dans la soiree.

Mais d'abord coffee time !

[Drizzt [Drone38]]

Ca marche !!!

Encore merci à vous deux, et PpPool c'est pas grave si tu t'es fait grillé, tu m'a fait gagné du temps en donnant ton code

--> Résolu.

[palindnilap]

Juste un petit avertissement : en utilisant une transaction autonome, cette transaction sera committée même si la commande qui a déclenché le trigger est annulée. A vous de voir si c'est un problème pour l'intégrité de vos données.

Je dis ça parce que j'ai failli le faire et que dans mon cas ça aurait été une grosse bêtise...