Algorithme pour contrainte CHECK

Version imprimable

13/11/2006, 15h26
ludvax

Algorithme pour contrainte CHECK

Bonjour à tous,

Le problème:
J'aimerai qualifié les données de ma colonne NOM et PRENOM pour qu'il n'y ai pas de chiffre !

Ma solution:
Faire un trigger qui pour chaque ligne ajouté vérifira les champs NOM et PRENOM à l'aide de la constraint CHECK.

Mon soucis:
Comment faire pour vérifié la présence de chiffre sans utilisé de PL/SQL ?

Pourriez vous me donnez vos idées ?

Merci d'avance

avec la contrainte suivante cela devrait fonctionner

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
SQL>create table sygale_tst
  2  (mon_champs varchar2(50) check (instr(translate(mon_champs,'0123456879','1111111111'),'1')=0) )
;
 
Table créée.
 
Ecoulé : 00 :00 :00.00
SQL>
SQL>insert into sygale_tst values ('Sygale');
 
1 ligne créée.
 
Ecoulé : 00 :00 :00.00
SQL>insert into sygale_tst values ('Sygale1');
insert into sygale_tst values ('Sygale1')
*
ERREUR à la ligne 1 :
ORA-02290: violation de contraintes (ETOILE.SYS_C007468) de vérification
 
 
Ecoulé : 00 :00 :00.00
SQL>select * from sygale_tst;
MON_CHAMPS
--------------------------------------------------
Sygale

13/11/2006, 16h07
ludvax

Merci c'est exactement ce que je rechercher !

Pour bien faire, dans mon trigger, il ne me reste plusqu'a géré l'exeception !

Good Job
Merci
13/11/2006, 16h26
Hugues_78
Tu as la solution suivante pour vérifier qu'il n'y a pas les caractères interdits...
Code:

1 2 3 create table toto (nom varchar2(100) constraint t_nom check (nom not like '%1%' and nom not like '%2%') )
Mais ça deviendra vite assez lourd...

Tu as oublié de nous préciser la version d'Oracle. Je sais qu'en 10G, ils ont ajouté les expressions régulières qui te permettront de répondre à tous tes besoins de manière bien plus simple.
13/11/2006, 16h44
Hugues_78

8O depuis que j'ai vu les expressions régulières, j'ai du mal à oublié... Tellement que j'avais oublié le translate
13/11/2006, 17h13
ludvax

Concernant ton code effectivement c'est ce à quoi j'avais pensé mais comme tu la fait remarqué ce serai lourd.

Concernant la version d'oracle "c'est un peu tout le problème" la version de test est sur 10G mais la version de prod est encore en 8i !

Comment puis-je géré plusieur CHECK dans le même TRIGGER ?
Plus précisement, quel est la syntaxe à utiliser entre BEGIN et le END ?

Merci par avance
13/11/2006, 17h47
Hugues_78
Je sais pas si tu connais un peu UNIX ou Perl. Les expressions régulières sont un format de recherche élaboré à l'intérieur d'une chaîne de caractères, qui te permettent entre autre de mettre des plages de code ascii.

pour vérifier qu'il n'y a que des caractères alphanumériques
Code:

1 2 ALTER TABLE EMP ADD CONSTRAINT REGEX01 CHECK (REGEXP_LIKE(ENAME,'^[[:alpha:]]+$'));
^ = début de la chaîne de caractères
[[:alpha:]] = classe de caractères alphanumériques
+ pour dire qu'il y a au moins un caractère...
$ = fin de la chaîne de caractères

Tu mettrait ^[A-Z][a-z]*$ pour avoir la première lettre en majuscule

^ = début de chaîne
[A-Z] = premier caractères dans cette plage (il est obligatoire)
[a-z] = caractère dans cette plage
* pour dire qu'il y en as 0, 1, ou plusieurs (le +, c'est 1 ou plusieurs)
$ = fin de la chaine de caractères

Un unixien pure race parle ça couremment. j'ai un peu oublié, mais ça revient très vite dés qu'on a compris la logique.

Avec un trigger, tu peux faire par exemple ça :

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
 
CREATE TABLE toto(ID NUMBER, nom VARCHAR2(100))
 
 
CREATE OR REPLACE TRIGGER test_trg
    BEFORE INSERT OR UPDATE
    ON toto
    FOR EACH ROW
DECLARE
    exception_ok   EXCEPTION;
    var_ok         NUMBER;
BEGIN
    IF :NEW.ID < 0 THEN
        raise_application_error ( num       => -20000
                                 ,msg       => 'ça marche pas parceque l''ID est négatif' );
    ELSE
        DECLARE
            err   BOOLEAN := FALSE;
        BEGIN
            FOR i IN 1 .. LENGTH ( :NEW.nom )
            LOOP
                IF ascii ( substr ( :NEW.nom, i, 1 )) BETWEEN 48 AND 58   -- 0 et 9
                                                                       THEN
                    err    := TRUE;
                END IF;
            END LOOP;
            IF err = TRUE THEN
                raise_application_error ( num       => -20000
                                         ,msg       => 'ça marche pas parceque le nom est faux'
                                        );
            END IF;
        END;
    END IF;
END;
/

Tu le met en before insert parceque l'insertion ne doit pas se faire si les données sont fausses

Tu le met pour each row pour tester ligne à ligne (dans le cas d'update global ou d'insert select)

Bonjour,

Afin d'ajouter les mauvaises valeurs dans une autre table, j'aimerai savoir si il est possible dans mon TRIGGER d'executer cette action (insert ds une autre table) ?

Ou quel serait votre méthode ?

Mon code ...

Code:

1
2
3
4
5
6
7
8
9
10
11
FOR i IN 1 .. LENGTH ( :NEW.nom )
        LOOP
            IF (ascii ( substr ( :NEW.nom, i, 1 )) NOT BETWEEN 65 AND 90) OR
               (ascii ( substr ( :NEW.nom, i, 1 )) NOT BETWEEN 97 AND 122) THEN
                err_NOM := TRUE;
            END IF;
        END LOOP;
 
IF err_NOM = TRUE OR err_PRENOM = TRUE THEN
            --inserer cette ligne dans une autre table
        END IF;

14/11/2006, 11h38
Hugues_78

Oui, tu peux faire des insert dans d'autres tables...

Jette un oeil dans la FAQ avec le mot clé : mutating (table mutante)... Tu auras les piéges à éviter avec les triggers

GRRR :aie:

Je ne comprend pas !

Mon trigger me semble pourtant correct ???

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
CREATE OR REPLACE
TRIGGER TRIG_BLABLA BEFORE INSERT ON GOOD_TABLE 
FOR EACH ROW 
DECLARE
fake_Exception EXCEPTION;
 
strNOM VARCHAR2(50);
strPRENOM VARCHAR2(50);
telfix VARCHAR2(16);
telmob VARCHAR2(16);
strEmail VARCHAR2(70);
 
BEGIN
  --Vérification du NOM
  strNOM := FUNC_CORRIGE(:NEW.NOM);
  IF strNOM IS NOT NULL AND LENGTH(strNOM)>2 THEN
    FOR i IN 1 .. LENGTH ( strNOM )
    LOOP
        IF (ascii ( substr ( strNOM, i, 1 )) NOT BETWEEN 65 AND 90) OR
           (ascii ( substr ( strNOM, i, 1 )) NOT BETWEEN 97 AND 122) THEN
            RAISE fake_ABO;
        END IF;
    END LOOP;
 
  ELSE
  RAISE fake_Exception;
  END IF;
  --instr(translate(mon_champs,'0123456879','1111111111'),'1')=0)
  --Vérification du PRENOM
  strPRENOM := FUNC_CORRIGE(:NEW.PRENOM);
  IF strPRENOM IS NOT NULL AND LENGTH(strPRENOM)>2 THEN
   FOR i IN 1 .. LENGTH ( strPRENOM )
    LOOP
        IF (ascii ( substr ( strPRENOM, i, 1 )) NOT BETWEEN 65 AND 90) OR
           (ascii ( substr ( strPRENOM, i, 1 )) NOT BETWEEN 97 AND 122) THEN
            RAISE fake_Exception;
        END IF;
    END LOOP;
  ELSE
    RAISE fake_Exception;
  END IF;
 
  --Vérification du TELEPHONE FIXE
  telfix := replace(:NEW.telephone_fixe,'.','');
  IF telfix IS NOT NULL
  THEN
    IF LENGTH(telfix) = 10 AND SUBSTR(telfix,1,1)='0' THEN
      FOR i IN 1 .. LENGTH ( telfix )
      LOOP
          IF (ascii ( substr ( telfix, i, 1 )) NOT BETWEEN 48 AND 57) THEN
              :NEW.telephone_fixe :='';
              GOTO verif_mobil;
          END IF;
      END LOOP;
    ELSE
      :NEW.telephone_fixe :='';
    END IF;
  END IF;
 
  --Vérification du TELEPHONE MOBILE
  <<verif_mobil>>
  telmob := replace(:NEW.telephone_mobile,'.','');
  IF telmob IS NOT NULL
  THEN
    IF LENGTH(telmob) = 10 AND SUBSTR(telmob,1,2)='06' THEN
      FOR i IN 1 .. LENGTH ( telmob )
      LOOP
          IF (ascii ( substr ( telmob, i, 1 )) NOT BETWEEN 48 AND 57) THEN
              :NEW.telephone_mobile :='';
              GOTO verif_email;
          END IF;
      END LOOP;
    ELSE
      :NEW.telephone_mobile :='';
    END IF;
  END IF;
 
  --Vérification de l'Email
  <<verif_email>>
  strEmail := replace(:NEW.email,'.','');
  IF strEmail IS NOT NULL
  THEN
    IF LENGTH(strEmail) < 5 AND INSTR(strEmail,'@')=0 THEN
      :NEW.email :='';
    END IF;
  END IF;
 
EXCEPTION
 WHEN fake_Exception THEN
--Requete insertion dans table FAKE
      INSERT INTO fake_..

Mais toute les lignes sont inseré dans les deux table Good_table et Fake !

Pourquoi ?

En levant l'exception dans mon 'TRIGGER' la DML d'insertion ne devrait-elle pas être annuler ?

C'est a n'y rien comprendre !

Please Help

15/11/2006, 16h37
ludvax

Trigger: Insert Malgres Exception

Bonjour a tous,

Malgrès l'exception l'ajout est quand même effectué !

Pourquoi ?

Que fais-je de mal ?

Merci d'avance
15/11/2006, 16h54
orafrance

il faut faire un RAISE dans l'exception.
16/11/2006, 10h08
ludvax

Ces à dire gérer une exception dans une exception ?

Je pense plutôt refaire l'algo !

Du style:
Dans le trigger (FOR EACH ROW) vérifié les conditions.

Dans un autre trigger s'executant à la fin et copiant les lignes non ajouter dans une table spécifique.