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

[sadok.m]

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

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

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

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

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

[skuatamad]

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.

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

[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;

[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 :

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.

[sadok.m]

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;

[sadok.m]

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

[skuatamad]

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 ?

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

[mnitu]

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

[Mohamed.Houri]

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.

[sadok.m]

Merci à tous !

[skuatamad]

Envoyé par Mohamed.Houri

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

Effectivement

[Waldar]

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 :

Ou plus simplement :

Code :

CREATE INDEX t1_fbi ON t1(case when v1 <> 'N' then 'X' end)

[skuatamad]

Envoyé par Waldar

Ou plus simplement :

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