[ORA-36]Oracle9i: Comment éviter la récursivité suite à l'exécution d'un trigger

Bonjour,

Voici mon problème:

La situation:
- j'ai une procédure stockée Y qui fait INSERT ou UPDATE dans une table X
- je veux modifier un champ de X (libellé) si son identifiant est modifié lors de la procédure Y
- pour éviter l'erreur de table mutante, j'ai créé 2 triggers:
1) un trigger de niveau LIGNE qui mémorise chacune des lignes modifiées de X avec un BEFORE or UPDATE on X FOR EACH ROW
Il exécute la mémorisation avant la modification de chaque ligne

2) un trigger de niveau INSTRUCTION avec un AFTER or UPDATE on X
Il autorise l'accés à la table mutante X au travers de la procédure Z créée pour faire l'UPDATE sur le libellé de X

Résultat:
Au chargement de X j'ai l'erreur: ORA-00036: maximum number of recursive SQL levels (string) exceeded
Il y a un problème de récursivité. Comment le résoudre ?

Merci d'avance pour toute suggestion.

Une solution serait peut-être de désactiver/réactiver le trigger dans la procédure autonome...

A tester.

Citation:

---

Envoyé par SheikYerbouti

Une solution serait peut-être de désactiver/réactiver le trigger dans la procédure autonome...

A tester.

---

Ok, mais quelle procédure autonome: la Y (UPDATE sur la table X) ou la Z (proc d'UPDATE appelée dans le trigger) ?

Celle qui met à jour la table sur laquelle se déclenche le trigger.

Citation:

---

Envoyé par SheikYerbouti

Celle qui met à jour la table sur laquelle se déclenche le trigger.

---

Bonjour,

J'ai compris ce qu'il faut faire et j'ai essayé ceci:

create or replace procedure Y is

begin
-- désactivation du trigger
alter table X disable trigger trig1;

begin
insert into X.....;
commit;
exception...
end;

begin
update X set......;
commit;
exception...;
end;

-- réactivation du trigger
alter table X enable trigger trig1;

end Y;
/

Le compilateur n'aime et j'ai le message d'erreur

PLS-00103: Encountered the symbol "ALTER" when expecting one of
the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe

Ma question: où et comment dois-je placer la désactivation du trigger ?

Il faut utiliser EXECUTE IMMEDIATE et être sûr d'avoir le privilège correspondant directement donné par un GRANT et non par un rôle.

1. une procédure autonome doit incluse lr mot clé:
PRAGMA AUTONOMOUS_TRANSACTION

2. Vous ne pouvez pas utiliser directement un ordre du DDL, vous devez l'encapsuler dans une instruction EXECUTE IMMEDIATE

Desactiver le trigger ? Et que se passe t il si quelqu'un d'autre met a jour X pendant ce temps. Le trigger ne se declenchera pas non plus et l'on mémorisera pas la modification de X.

Je n'ai malheureusement pas la solution mais la desactivation du trigger ne me semble pas une bonne voie.

Citation:

---

Envoyé par Ito

La situation:
- j'ai une procédure stockée Y qui fait INSERT ou UPDATE dans une table X
- je veux modifier un champ de X (libellé) si son identifiant est modifié lors de la procédure Y
- pour éviter l'erreur de table mutante, j'ai créé 2 triggers:

---

Salut,
Je n'arrive pas à comprendre le fonctionnement

1/ Tu fais des INSERT ou UPDATE par une procédure Y
2/ Tu ne veux modifier le champ X(lib) que si le champ X'(id) a été modifié par la procédure Y et pas par une autre

Si c'est ça, rajoute une colonne PROC NUMBER(1)
Dans ta proc Y tu mets à jour cette colonne PROC = 1
Tu crées un trigger sur X(id) et tu testes si proc=1

Code:

---

1 2 3 4 5 6 7

AFTER UPDATE OF X(id) FOR EACH ROWS WHEN (:new.Proc = 1) BEGIN :new.proc := NULL; :new.X(lib) := 'modif'; END;

---

l'idéal c'est de créer une variable de session (une variable dans un package) et de modifier sa valeur pour indiquer au trigger qu'il ne doit pas se déclencher

Yop, bien plus net.

Petite question en rapport avec la solution de Fred_D.

Comment faire pour que le trigger sache qu'il ne doit pas s'executer ?

Est-ce que tu veux juste dire faire un (en resume) if <variable_global> à 0 then exit;

ou peut-on ajouter dans les conditions de declanchement du trigger (avant le begin) quelque chose la dessus.

Car dans mon cas, j'ai un trigger que j'aimerai bien desactiver pour la duree d'une procédure mais je ne peux pas car d'autres traitements peuvent avoir besoin de ce trigger.
Ce qui pose probleme ce n'est pas tant le traitement qu'effectue le trigger mais plutot le temps d'execution. Ma procédure faisant beaucoup de mise à jour declancheuse du trigger, meme avec un trigger vide ca prends enormement de temps. Alors qu'avec un trigger inactif c'est tres rapide. L'ideal serait donc une solution ne provoquant pas du tout l'activation du trigger tout en le laissant actif pour d'autres procédures.

Merci d'avance pour d'éventuelles idées.

Tu dis que ton trigger met à jour un champ Y quand le champ X est modifié.
Doit-il aussi se déclencher quand tu mets à jour le champ Y ?
Si non, le plus simple est de préciser dans ton trigger qu'il ne se déclenche que si le champ X est modifié ?
Quelque chose du genre

Code:

---

1 2 3

trigger toto_avant before update of champ X on la_table for each row...   trigger toto_apres after update of "champ X" on la_table ...

---

C'est pas exactement ça, j'ai mal du m'exprimer, je vais reouvrir un poste ou je vais mieux decrir mon problème ça sera plus simple.