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