IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Oracle Discussion :

Problème de table mutante - count dans le trigger appliqué à la même table


Sujet :

Oracle

  1. #1
    Membre actif
    Inscrit en
    Avril 2009
    Messages
    22
    Détails du profil
    Informations forums :
    Inscription : Avril 2009
    Messages : 22
    Par défaut Problème de table mutante - count dans le trigger appliqué à la même table
    Bonjour à tous,
    Je sais que ce problème a été reporté mainte fois, mais je n'arrive pas à trouver une solution sans pour autant avoir à créer d'autre package ou
    d'autre trigger, ou une table temporaire..

    J'ai le trigger suivant
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    CREATE OR REPLACE TRIGGER company_class_pre_ins
    BEFORE INSERT  ON g_dosser 
    FOR EACH ROW
    ....
    SELECT COUNT(*)
    INTO existing_doss_cnt
    FROM g_dosser doss
    WHERE doss.doss_num= :new.doss_num
    AND   doss.doss_type= :new.doss_type;

    et un autre trigger qui contient la requête suivante (donc lorsqu'on a un tel insert le premier trigger se déclenche)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    INSERT INTO g_dosser(
                    doss_num,
                    doss_type,
                    doss_code,
                    doss_group_code,
                    doss_id_fl)    
    SELECT      :new.dbd_num,
                    dbd_type,
                    decode( dbd_ind, 'i', 'itnn', 'a', 'itnn', NULL ),
                    decode( dbd_ind, 'b', 'rppd', 'a', 'rppd', NULL ),
                    'n'
    FROM    dbd_type
    WHERE   :new.dbd_flag = 'y';
    je reçois effectivement l'erreur ORA-20700: ORA-04091 table is mutating...

    je sais que le problème provient du premier count(*) puisqu'il est appliqué sur la table elle même, mais je n'arrive pas à contourner cette erreur
    Merci pour votre aide

  2. #2
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 197
    Billets dans le blog
    1
    Par défaut
    Si je ne m'abuse, il faut faire ton select dans une "autonomous transaction", c'est à dire une transaction extérieure à la transaction actuelle. Attention cependant, cela implique qu'elle n'a pas accès aux données que tu es en train de modifier.

  3. #3
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Il y a un tutoriel sur le problème de la table mutante sur ce site, mais le meilleur c’est de ne pas utiliser les triggers dans ce cas.

  4. #4
    Membre Expert

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Par défaut
    Comme toujours, que vaudrait votre count(*) dans un environnement multi-users?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT COUNT(*)
    INTO existing_doss_cnt
    FROM g_dosser doss
    WHERE doss.doss_num= :new.doss_num
    AND   doss.doss_type= :new.doss_type;
    L'erreur "ORA-20700: ORA-04091 table is mutating" est là pour vous protéger et non pour vous géner.

    Si vous embarquez dans cette direction en voulant éviter l'erreur de la table mutante en y ajoutant une dose de transaction autonome, vous êtes alors dans une mauvaise direction.

  5. #5
    Membre actif
    Inscrit en
    Avril 2009
    Messages
    22
    Détails du profil
    Informations forums :
    Inscription : Avril 2009
    Messages : 22
    Par défaut
    Je ne comprends pas votre question Mohamed.

    je fais actuellement ce count, parce que aprés j'ai des contrôles à faire sur le type de dossier à insérer,
    certains types devront être unique.

    Juste une remarque, lorsque je fais un simple insert
    insert into g_dossier values (....)
    je n'ai pas le problème de la table mutante.

  6. #6
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 952
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 952
    Par défaut
    je fais actuellement ce count, parce que aprés j'ai des contrôles à faire sur le type de dossier à insérer, certains types devront être unique.
    Comment sont déterminés les types qui devoivent être uniques ?
    Si la condition est simple et surtout déterministe, alors un FBI (Function Based Indexes) devrait résoudre le problème.
    Juste une remarque, lorsque je fais un simple insert
    insert into g_dossier values (....)
    je n'ai pas le problème de la table mutante.
    Dans quelles circonstances l'erreur ORA-04091 se produit-elle ?

    Mais je pense comme Mnitu et Mohamed que le mieux est d'éviter les triggers dans ce cas, surtout si la contrainte s'exprime facilement.

  7. #7
    Membre actif
    Inscrit en
    Avril 2009
    Messages
    22
    Détails du profil
    Informations forums :
    Inscription : Avril 2009
    Messages : 22
    Par défaut
    Merci pour votre réponse skuatamad,
    En fait, j'ai deux trigger, c'est dans le premier (table b_organisation) qu'il y'a un insert en masse (INSERT INTO g_dosser ... select * from dbd_type) qui déclenche le deuxième trigger sur la table (g_dosser) , d'ou le count(*)
    le type de dossier qui doit être unique est comparé en dur genre

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    IF existing_doss_cnt >0
        THEN
         IF :new.doss_type = 'CHG'
    ....
     
    ELSE
      --- type de dossier autre que CHG ne doit pas être multiple.
       RAISE err;
    END IF;
    END IF;

    comme signalé, si c'est un insert simple je n'ai pas le problème de la table mutante, ceci se déclenche juste avec l'insert en masse.

  8. #8
    Membre Expert

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Par défaut
    Si votre but est d'eviter des doublons de dossiers alors

    (a) implémentez une contraint unique sur le dossier
    ou
    (b) un index unique (peut-être dans votre case un function based index)

    Ainsi, vous n'aurez pas à vous souciez du problème de concurrence lorsque deux utilisateurs voudront ajouter le même dossier. Le deuxième attendra la fin du travail du premier avant soit d'être rejeté grâce à (a) ou (b) ou que son insert soit accepté à cause du fait que le premier user a fait un rollback;

  9. #9
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 952
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 952
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    create unique index type_chg_uk on g_dosser (CASE WHEN doss_type <> 'CHG' THEN doss_type ELSE NULL END);
    Comme les NULL ne seront pas indexés, tous les doss_type différents de CHG seront dans un index unique et ceux égaux à CHG n'y seront pas (donc pas de doublon).
    Et donc pas de trigger, pas de code, pas d'erreur de table en mutation

    Il y a des exemples dans la doc sur CREATE INDEX :
    Using a Function-based Index to Define Conditional Uniqueness: Example The following statement creates a unique function-based index on the oe.orders table that prevents a customer from taking advantage of promotion ID 2 ("blowout sale") more than once:

    CREATE UNIQUE INDEX promo_ix ON orders
    (CASE WHEN promotion_id =2 THEN customer_id ELSE NULL END,
    CASE WHEN promotion_id = 2 THEN promotion_id ELSE NULL END);

    INSERT INTO orders (order_id, order_date, customer_id, order_total, promotion_id)
    VALUES (2459, systimestamp, 106, 251, 2);
    1 row created.

    INSERT INTO orders (order_id, order_date, customer_id, order_total, promotion_id)
    VALUES (2460, systimestamp+1, 106, 110, 2);
    insert into orders (order_id, order_date, customer_id, order_total, promotion_id)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (OE.PROMO_IX) violated


    The objective is to remove from the index any rows where the promotion_id is not equal to 2. Oracle Database does not store in the index any rows where all the keys are NULL. Therefore, in this example, we map both customer_id and promotion_id to NULL unless promotion_id is equal to 2. The result is that the index constraint is violated only if promotion_id is equal to 2 for two rows with the same customer_id value.

  10. #10
    Membre actif
    Inscrit en
    Avril 2009
    Messages
    22
    Détails du profil
    Informations forums :
    Inscription : Avril 2009
    Messages : 22
    Par défaut
    Merci skuatamad et Mohamed
    skuatamad ça aura pu être la solution adéquate si mon trigger se limitait juste à controler les doublons, plus bas dans le trigger j'ai pratiquement le même count qui controle si pour le type 'CHG' on a un champ default_doss_flag a 'Y'.

    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
    SELECT count(*)
            INTO   default_flag_doss
            FROM   g_dossier doss
            WHERE doss.doss_num= :new.doss_num
            AND   doss.doss_type= :new.doss_type
            AND   doss.default_doss_flag = 'Y';
     
    IF ( :new.default_doss_flag = 'Y' and default_flag_d >0 )
            THEN
                    raise too_many_defaults;
            END IF;
            IF ( :new.default_doss_flag = 'N' and default_flag_d = 0 )
            THEN
                    raise need_one_default;
            END IF;
    END IF;

  11. #11
    Membre actif
    Inscrit en
    Avril 2009
    Messages
    22
    Détails du profil
    Informations forums :
    Inscription : Avril 2009
    Messages : 22
    Par défaut
    Je viens de changer l'insert en masse comme suit, sans pour autant avoir à éradiquer le count de l'autre trigger

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    FOR record IN (SELECT * FROM dbd_type WHERE  :NEW.dbd_flag = 'Y') LOOP 
    insert into g_dosserdoss_num
    			 doss_type
    			 doss_code
    			 doss_group_code
    			 doss_id_fl
    			 values(:NEW.dbd_num,
    			 record.dbd_type,
    			 DECODE( record.dbd_ind, 'I', 'ISTR', 'A', 'ISTR', NULL ),
        			 DECODE( record.dbd_ind, 'B', 'BORG', 'A', 'BORG', NULL ),
        			 'N');
    END LOOP ;
    et Il semble que ça marche, parce que (à vrai dire) les inserts se font ligne par ligne, et non pas en masse.

    Est ce que ce que j'ai dit est sensé ?

  12. #12
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 952
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 952
    Par défaut
    Pour ce cas :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    IF ( :new.default_doss_flag = 'Y' AND default_flag_d >0 )
            THEN
                    raise too_many_defaults;
            END IF;
    il suffit de complexifier un peu la contrainte, comme ça je pense :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE UNIQUE INDEX type_chg_uk ON g_dosser 
                        (CASE WHEN doss_type <> 'CHG' OR (doss_type = 'CHG' AND default_doss_flag = 'Y')
                              THEN doss_type 
                              ELSE NULL 
                          END ,
                         CASE WHEN doss_type <> 'CHG'
                              THEN 'A'
                              WHEN doss_type = 'CHG' AND default_doss_flag = 'Y'
                              THEN default_doss_flag
                              ELSE NULL
                          END);
    Par contre pour le "au moins 1 flag à Y" :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    IF ( :new.default_doss_flag = 'N' AND default_flag_d = 0 )
           THEN
                    raise need_one_default;
            END IF;
    Ca n'est pas possible sur Oracle avec une simple contrainte CHECK.
    Regarde ce post pour de plus amples informations : Contraintes complexes ?

    et Il semble que ça marche, parce que (à vrai dire) les inserts se font ligne par ligne, et non pas en masse.

    Est ce que ce que j'ai dit est sensé ?
    Oui ça a un sens en même temps dans l'exemple tu n'essaie plus d'insérer dans g_dosser mais dans g_dosserdoss_num...

  13. #13
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Quand vous aurez fini d’écrire vos triggers lisez l’article « The trouble with triggers ».

  14. #14
    Membre Expert

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Par défaut
    Marius m'a précédé concernant l'article de Tom Kyte sur les triggers.

    @skuatamad,

    attention à la valeur null du doss_type lors de la création d'un indexe unique du type fonction comme vous l'avez très justement conseillé.

    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
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
     
    mhouri > create table t1 (
      2  	v1 varchar2(10),
      3  	n1 number
      4  );
     
    Table created.
     
    mhouri > insert into t1 values('A',1);
     
    1 row created.
     
    mhouri > insert into t1 values('N',2);
     
    1 row created.
     
    mhouri > insert into t1 values(null,3);
     
    1 row created.
     
    mhouri > commit;
     
    Commit complete.
     
    mhouri > select * from t1 where case when v1  = 'N' then null else 'X' end = 'X';
     
    V1                 N1                                                           
    ---------- ----------                                                           
    A                   1                                                           
                        3                                                           
     
    mhouri > select * from t1 where case when nvl(v1,'N') = 'N' then null else 'X' end = 'X';
     
    V1                 N1                                                           
    ---------- ----------                                                           
    A                   1
    C'est pour cela que l'index (dans le cas où dos_type peut être null) doit être pensé comme suit:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    mhouri > create index t1_fbi on t1(case when nvl(v1,'N') = 'N' then null else 'X' end);
     
    Index created.

  15. #15
    Membre actif
    Inscrit en
    Avril 2009
    Messages
    22
    Détails du profil
    Informations forums :
    Inscription : Avril 2009
    Messages : 22
    Par défaut
    Merci à tous !

  16. #16
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 952
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 952
    Par défaut
    Citation Envoyé par Mohamed.Houri Voir le message
    attention à la valeur null du doss_type lors de la création d'un indexe unique
    Effectivement

  17. #17
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Citation Envoyé par Mohamed.Houri Voir le message
    C'est pour cela que l'index (dans le cas où dos_type peut être null) doit être pensé comme suit:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    mhouri > create index t1_fbi on t1(case when nvl(v1,'N') = 'N' then null else 'X' end);
    Ou plus simplement :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    create index t1_fbi on t1(case when v1 <> 'N' then 'X' end)

  18. #18
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 952
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 952
    Par défaut
    Citation Envoyé par Waldar Voir le message
    Ou plus simplement :
    Donc j'avais bon? .... je ne sais plus ....je suis perdu....

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Problème de droits d'éxécution dans un trigger
    Par Xavinou57 dans le forum PL/SQL
    Réponses: 3
    Dernier message: 05/11/2009, 20h43
  2. Count dans plusieurs colonne d'une même table
    Par macfleid dans le forum Langage SQL
    Réponses: 7
    Dernier message: 15/07/2008, 20h43
  3. Trigger sur une même table
    Par lamanoo dans le forum DB2
    Réponses: 6
    Dernier message: 29/08/2007, 17h01
  4. Réponses: 2
    Dernier message: 13/06/2007, 12h29
  5. Réponses: 1
    Dernier message: 28/03/2007, 12h23

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo