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

Version imprimable

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:

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:

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

13/10/2011, 16h28
StringBuilder

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.
13/10/2011, 16h29
mnitu

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.
13/10/2011, 16h46
Mohamed.Houri
Comme toujours, que vaudrait votre count(*) dans un environnement multi-users?
Code:

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.
13/10/2011, 17h00
sadok.m

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.
13/10/2011, 17h15
skuatamad

Citation:

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.

Citation:

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.
13/10/2011, 17h24
sadok.m
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:

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.
13/10/2011, 17h29
Mohamed.Houri

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;
13/10/2011, 17h39
skuatamad

Code:

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 :

Citation:

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.

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:

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;

Je viens de changer l'insert en masse comme suit, sans pour autant avoir à éradiquer le count de l'autre trigger
Code:

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é ?

Pour ce cas :

Code:

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:

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:

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 ?

Citation:

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...

14/10/2011, 08h44
mnitu

Quand vous aurez fini d’écrire vos triggers lisez l’article « The trouble with triggers ».

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:

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:

1
2
3
4
 
mhouri > create index t1_fbi on t1(case when nvl(v1,'N') = 'N' then null else 'X' end);
 
Index created.

14/10/2011, 10h36
sadok.m

Merci à tous !
14/10/2011, 10h59
skuatamad

Citation:

Envoyé par Mohamed.Houri

attention à la valeur null du doss_type lors de la création d'un indexe unique

Effectivement :ccool:
14/10/2011, 14h22
Waldar
Citation:
Envoyé par Mohamed.Houri

C'est pour cela que l'index (dans le cas où dos_type peut être null) doit être pensé comme suit:

Code:

1 2 mhouri > create index t1_fbi on t1(case when nvl(v1,'N') = 'N' then null else 'X' end);
Ou plus simplement :

Code:

create index t1_fbi on t1(case when v1 <> 'N' then 'X' end)
15/10/2011, 01h30
skuatamad

Citation:

Envoyé par Waldar

Ou plus simplement :

Donc j'avais bon? .... je ne sais plus ....je suis perdu.... :aie: