Oracle: Contraintes d'intégrités

Version imprimable

16/10/2006, 16h39
BOUHAMOU

Oracle: Contraintes d'intégrités

Bonjour à tous,

J'ai une table qui contiens un range max et un range min lié à une instance d'un composant comme suivant:
Min Max Instance

Min, Max , Instance se sont des entiers réels.
example d'utilisation:
Min | Max | Instance
0 10 6
11 20 5

je souhaite verifier avec les contraintes d'intégrités le fait de ne pas permettre l'insertion de deux intervalles qui se chauvauche dans la table.

Example de cas d'erreur souhaité:
Min | Max | Instance
0 10 6
5 20 5

Je ne souhaite pas permettre aux utilisateurs d'inserer ce genre d'information.
Il faut absolument que les intervalles ne se chauvauche pas sauf si l'instance est la meme.
example:

Min | Max | Instance
0 10 6
5 20 6
dans ce cas, c ok!

Pouvez vous m'aider pour mettre en place un mechanise pour garantir l'intégrité des information de ma table?

Merci,

Voilà un problème interressant :)

Je ne sais pas si les dernières versions d'oracle contiennent des objets qui font tout seul. Moi j'ai déja mis en place ce genre de test, il faut jouer fin car on est confronté à la fameuse erreur "table en mutation". Pour contourner ce problème, j'avais fait en 2 temps:
- insertion dans une table temporaire de toutes les modif ligne à ligne
- trigger de test AFTER STATEMENT qui lui peut contenir des requête sur la table (qui n'est plus en mutation)

Dans l'exemple que je donne, je reste très général avec une table à simplement 2 colonnes (début et fin). Il faudra que tu l'adapte avec ton test supplémentaire sur les instances.
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 37 38 39 40 41 -- TABLE DE TEST create table TEST_CHEV (DEB integer, FIN integer ) -- TABLE TEMPORAIRE A L'IMAGE DE LA TABLE DE TEST AVEC LE ROWID create GLOBAL TEMPORARY table TMP_CHEV (DEB integer, FIN integer, RID ROWID) -- remplissage de la table temporaire au fil de l'eau car on ne peut pas faire de select dans la table CREATE OR REPLACE TRIGGER TRG_AIU_CHEV AFTER INSERT OR UPDATE ON TEST_CHEV REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN insert into TMP_CHEV (DEB , FIN , RID) values (:NEW.DEB, :NEW.FIN , :NEW.ROWID); END ; / -- verification apres requete: ici on peut faire un select dans la table CREATE OR REPLACE TRIGGER TRG_AS_CHEV AFTER INSERT OR UPDATE ON TEST_CHEV REFERENCING NEW AS NEW OLD AS OLD DECLARE nn integer; BEGIN -- test d'existance de chevauchement -- ici avec des inégalités larges car on ne tolère pas qu'un début soit égal à une fin. select count(1) into nn from TEST_CHEV T, TMP_CHEV TMP where T.ROWID != TMP.RID and T.DEB <= TMP.FIN and T.FIN >= TMP.DEB; -- purge de la table temporaires delete from TMP_CHEV; -- declenchement d'exception, le statement sera annulé. if nn > 0 then RAISE_APPLICATION_ERROR (-20000,'CHEVAUCHEMENT INTERDIT!'); end if; END ; /

16/10/2006, 21h27
pifor

L'idée du trigger AFTER STATEMENT est intéressante mais je pense que cette contrainte simple en apparence mais pas si simple à coder nécessite quelque chose de plus. En effet 2 transactions indépendantes l'une de l'autre peuvent insérer chacune une ligne individuellement correcte mais dont le résultat complet après COMMIT des 2 transactions ne respecte plus la contrainte:

Exemple:
T1: insert into t(cmin, cmax, cinstance) values (100, 110, 6);
T2: insert into t(cmin, cmax, cinstance) values (100, 120, 5);
T1: commit => le trigger ne voit que (100, 110, 6): OK
T2: commit => le trigger ne voit que (100, 120, 5)): OK

Et on a dans la base:
(100, 110, 6) et (100, 120, 5): KO.

Il faudrait essayer de sérialiser avec DBMS_LOCK comme dans l'exemple suivant: http://asktom.oracle.com/pls/ask/f?p...A:474221407101
16/10/2006, 23h34
remi4444
:koi:

effectivement j'avais pas traité le problème des acces concurents... un petit dmbs_lock en début de trigger "after statement" résoud....

effectivement aussi on a besoin de ne stocker que les rowid, à voir s'il vaut mieux faire une table dans un package ou une table temporaire....

Par contre je trouve le test de chevauchement de Tom très laid... je préfère le miens... :P

En imaginant qu'on ne stocke que les rowid dans la table temporaire que j'avais donné en exemple, le test donnerait:
Code:

1 2 3 4 5 SELECT count(1) INTO nn FROM TEST_CHEV T, TMP_CHEV R, TEST_CHEV TR WHERE TR.ROWID = R.RID AND T.ROWID != R.RID AND T.DEB <= TR.FIN AND T.FIN >= TR.DEB;
C'est un premier jet à l'aveugle, je testerais demain pour voir ce que ça donne, ça m'interresse de trouver une solution propre et solide à ce problème.... ;)

Voilà remanié et testé avec le lock et la simplification de la table temporaire:

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
37
38
39
40
41
42
43
44
 
-- TABLE DE TEST 
create table TEST_CHEV (DEB integer, FIN integer );
 
-- TABLE TEMPORAIRE A L'IMAGE DE LA TABLE DE TEST AVEC LE ROWID 
create GLOBAL TEMPORARY table TMP_CHEV (RID ROWID);
 
-- remplissage de la table temporaire au fil de l'eau car on ne peut pas faire de select dans la table 
CREATE OR REPLACE TRIGGER TRG_AIU_CHEV
AFTER INSERT OR UPDATE
ON TEST_CHEV 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  insert into  TMP_CHEV ( RID)
    values (:NEW.ROWID);
END ;
/
 
-- verification apres requete: ici on peut faire un select dans la table 
CREATE OR REPLACE TRIGGER TRG_AS_CHEV
AFTER INSERT OR UPDATE
ON TEST_CHEV 
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
 nn integer;
BEGIN
    -- blocage de la table
    lock table TEST_CHEV in exclusive mode;
   -- test d'existance de chevauchement
   -- ici avec des inégalités larges car on ne tolère  pas qu'un début soit égal à une fin.
   SELECT count(1) INTO nn  FROM TEST_CHEV T, TMP_CHEV R, TEST_CHEV TR
    WHERE  TR.ROWID = R.RID
      AND T.ROWID != R.RID 
	  AND T.DEB <= TR.FIN
	  AND T.FIN >= TR.DEB;
   -- purge de la table temporaires
   delete from TMP_CHEV;
   -- declenchement d'exception, le statement sera annulé.
   if nn > 0 then
     RAISE_APPLICATION_ERROR (-20000,'CHEVAUCHEMENT INTERDIT!');
   end if;
END ;
/

Adaptation de la solution au problème précis (j'ai changé le nom des colonne MIN et MAX qui sont des mots reservés oracle):

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
37
38
39
40
41
42
43
44
45
-- TABLE DE TEST 
create table TEST_INST (RMIN integer, RMAX integer, INST integer );
 
-- TABLE TEMPORAIRE A L'IMAGE DE LA TABLE DE TEST AVEC LE ROWID 
create GLOBAL TEMPORARY table TMP_INST (RID ROWID);
 
-- remplissage de la table temporaire au fil de l'eau car on ne peut pas faire de select dans la table 
CREATE OR REPLACE TRIGGER TRG_AIU_INST
AFTER INSERT OR UPDATE
ON TEST_INST 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  insert into  TMP_INST (RID)
    values (:NEW.ROWID);
END ;
/
 
-- verification apres requete: ici on peut faire un select dans la table 
CREATE OR REPLACE TRIGGER TRG_AS_INST
AFTER INSERT OR UPDATE
ON TEST_INST 
REFERENCING NEW AS NEW OLD AS OLD
DECLARE
 nn integer;
BEGIN
    -- blocage de la table
    lock table TEST_INST in exclusive mode;
   -- test d'existance de chevauchement
   -- ici avec des inégalités larges car on ne tolère  pas qu'un début soit égal à une fin.
   SELECT count(1) INTO nn  FROM TEST_INST T, TMP_INST R, TEST_INST TR
    WHERE  TR.ROWID = R.RID
      AND T.ROWID != R.RID 
	  AND T.INST != TR.INST
	  AND T.RMIN <= TR.RMAX
	  AND T.RMAX >= TR.RMIN
	  AND ROWNUM <= 1; /* optim */ 
   -- purge de la table temporaires
   delete from TMP_INST;
   -- declenchement d'exception, le statement sera annulé.
   if nn > 0 then
     RAISE_APPLICATION_ERROR (-20000,'CHEVAUCHEMENT INTERDIT POUR DES INSTANCES DIFFERENTES');
   end if;
END ;
/

Dans ce cas le lock complet de la table est logique fonctionnellement puisque la cohérence se calcule sur l'ensemble de la table donc 2 transactions ne peuvent pas attaquer cette table en parallèle. Si le problème avait été de controler la cohérence par catégorie dans une meme table, on aurait pu imaginer un lock plus fin...