Discussion :

[NPortmann]

Voici mon problème.
La table "QUESTIONNAIRES" contient une colonne qui spécifie un numéro d'ORDRE.
Avant une insertion ou une modification d'un enregistrement de la table "QUESTIONNAIRES", il faut que le système effectue un décalage (incémentation de 1) de la colonne d'ordre pour tous les enregistrements de la table QUESTIONNAIRES qui ont un ordre plus grand ou égal à l'ordre que je suis en train de modifier ou insérer.
La première idée est de réaliser un trigger BEFORE INSERT OR UPDATE sur la table questionnaire du style:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE OR REPLACE TRIGGER TR_ORDRE_VAR
BEFORE INSERT OR UPDATE
ON QUESTIONNAIRES
FOR EACH ROW
 
BEGIN
  UPDATE QUESTIONNAIRES SET ORDRE = ORDRE+1
  WHERE ... //jointure adéquoite
  AND ORDRE           >=:NEW.ORDRE;

Cette tentative n’est pas fonctionnel et renvoie l’erreur de la table en mutation. En effet, on ne peut pas mettre à jour des tuples d’une table que l’on est en train de modifier!
Pour contourner cette problèmatique, j'essaie de mettre en oeuvre un trigger qui envoi ma commande d'update dans une table ORDRE_SQL. Cette table est ensuite vidée toutes les 2 seconde par une procédure exécuter par le programmateur (scheduler) Oracle (sorte de réplication asymétrique asynchrone mais sur la même table).
Cette solution n'est pas loin de fonctionner. Mais avant d'aller plus loin, j'aimerais savoir si quelqu'un a une meilleur idée???
Merci.
P.S.: Si vous n'avez pas de meilleures ideés, vous pouvez peut-être me dire pourquoi la désactivation d'une contrainte pose problème dans un trigger.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE OR REPLACE TRIGGER TR_ORDRE_VAR
BEFORE INSERT OR UPDATE
ON QUESTIONNAIRES
FOR EACH ROW
DECLARE
	VL_ORDRE_SQL VARCHAR2(2000);
BEGIN
. . .
  ALTER TABLE QUESTIONNAIRES DISABLE CONSTRAINT QRE_ORDRE_UK;
...

renvoie l'erreur suivante:

Code :

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

1
2
3
4
5
6
7
8
9
 
PLS-00103: Symbole "ALTER" rencontré à la place d'un des symboles
suivants :
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

PS'': L'article http://sgbd.developpez.com/oracle/ora-04091/ parle de l'erreur de la table mutante, mais je n'ai pas trouvé de solution à mon problème dans cet article...
Voilà, merci.

[Xo]

Salut,

la problématique fonctionnelle à l'air ardue, modifier une partie des éléments d'une table quand tu en insères un me paraît fort contraignant. Peut-être que ta procédure d'INSERT devrait faire l'objet d'une procédure/transaction dédiée, avec décalage puis INSERT, le commit/rollback étant fait en fin de transaction afin de garantir l'intégrité de tes données.

En outre, les ordres DDL (Data definitin langage) ne sont pas acceptés dans le langage procédural (donc trigger). Si tu dois vraiment en utiliser, regarde du côté de EXEC. Voici un exempel de procédure qui permet l'insertion de tel ordres dans du PL/SQL :

Code :

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

1
2
3
4
5
6
7
8
9
10
   procedure sqlExecute (sql_cmd in varchar2)
   IS
      id_curseur integer;
      nbLign_curseur integer;
   BEGIN
      id_curseur := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE (id_curseur, sql_cmd, DBMS_SQL.NATIVE);
      nbLign_curseur := DBMS_SQL.EXECUTE (id_curseur);
      DBMS_SQL.CLOSE_CURSOR (id_curseur);
   END sqlExecute;

[orafrance]

EXECUTE IMMEDIATE remplace DBMS_SQL

[childeric]

premier solution : utilisation d'une séquence pour la génération du numéro, avec une recherche de la valeur par trigger, comme cela il n'y a plus de problèmes d'update de numéro plus grand

deuxième solution : crée une nouvelle colone pour stoker le numéro puis ensuite faire une procédure qui écrase le numéro par celui stoké dans votre nouvelle colonne

[thg]

Concernant la 4091 :

effectivement , dans un trigger for each row, il n'est pas possible de "toucher" la table sur laquelle le trigger est basé ...
Une solution peut etre de recuperer les "rowids" impactés via le trigger on each row et les sauver dans une table de rowid declarée en global dans un package PL .... ensuite il faut creer un trigger en after insert (sans la for each row) qui appele une procedure qui vient lire la table global de rowid et realise l'update ....

y a pas mal de notes la dessus sur Metalink ....