Problème Trigger CREATE

Version imprimable

Bonjour à tous, j'ai un problème avec un trigger que j'essaye de créer sous Oracle 10G Express.
J'ai une table qui représente les frontières entre 2 pays de la manière suivante :
Code:

1 2 Borders[country1,country2,length]
Et je voudrais que si la ligne (A,B,x) existe dans la table on ne puisse pas insérer les valeurs (B,A,x).

Sauf erreur de ma part , le trigger est la seul solution pour gérer ce type de doublons, j'ai donc essayer de créer un trigger :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE OR REPLACE TRIGGER check_doublons BEFORE INSERT ON Borders FOR EACH ROW DECLARE country1 NUMBER(15); country2 NUMBER(15); BEGIN IF :NEW.country1 = NEW.country2 THEN IF:OLD.country2 = NEW.country1 THEN RAISE_APPLICATION_ERROR(-20555, 'Le couple inverse existe déja !'); END IF; END IF; END
Mais lorsque que je déclare ce trigger j'obtiens le code d'erreur :
ERREUR à la ligne 4 : PLS-00201: l'identificateur 'OLD.COUNTRY2' doit être déclaré

J'ai du mal a voir l'erreur, peut etre y a t-il quelque chose que j'ai mal compris.

Merci d'avance pour votre aide :D

16/10/2008, 16h59
rvfranck

Dans un déclencheur INSERT t'as pas de OLD, bref il faut null.
16/10/2008, 17h09
arno15
Merci pour ta réponse !
OK pour le OLD, mais du coup ou dois-je mettre le null dont tu me parles ? j'imagine que ca n'est pas :
Code:

1 2 null.country2
Comment pourrai-t'on vérifier que le couple inverse de celui que l'on veut insérer n'existe pas déja dans la base ?
16/10/2008, 17h33
rvfranck
Je suppose que dans ta table, country1 + country2 c'est la clé primaire et que country1 est différent de country2.

A mon avis, après une insertion tu dois faire un SELECT dans ton trigger pour vérifier que le couple n'existe pas, mais tu risque d'avoir un problème de table mutante (ORA-04091) que tu peux contourner comme ça.

Pour revenir à mon idée, tu fais un truc du genre:
Code:

1 2 3 4 SELECT length FROM borders WHERE country1 IN(:new.country1,:new.country2) AND country2 IN(:new.country1,:new.country2)
Si la requete renvoie "NULL" (tu dois capturer cette erreur) le couple n'existe pas, mais si la requete renvoit une valeur alors le couple existe.

Avec cette methode tu devras capturer l'erreur du NULL renvoyée par le SELECT et tu devras voir comment resoudre le problème de table mutante

quelque chose comme ça:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create or replace TRIGGER CHECK_DOUBLONS_AFTER
AFTER INSERT ON BORDERS
FOR EACH ROW 
declare
nbre NUMBER DEFAULT 0;
BEGIN
  SELECT count (*) INTO nbre
  FROM borders
  WHERE country1 IN (:new.country1, :new.country2)
  AND country2 IN (:new.country1, :new.country2);
 
  if (nbre = 0) THEN
    dbms_output.put_line('Le couple existe déjà');
    DELETE FROM borders
    WHERE country1 = :new.country1
    AND country2 = :new.country2;
  end if;
 
  exception
     when OTHERS THEN
      dbms_output.put_line('Erreur dans le trigger: ' || SQLERRM);
END;

Re, j'ai appliqué les méthode de résolution de la table mutante pour mon probleme, j'ai donc crée 2 déclencheurs, 1 BEFORE et 1 AFTER :

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
 
CREATE GLOBAL TEMPORARY TABLE tempBorders AS (
		SELECT country1,country2 
			FROM Borders WHERE 0=1
		);
 
CREATE OR REPLACE TRIGGER check_doublons_before BEFORE INSERT ON Borders
FOR EACH ROW
BEGIN
	INSERT INTO tempBorders(country1,country2) VALUES       (:NEW.country1,:NEW.country2);
END;
 
 
CREATE OR REPLACE TRIGGER check_doublons_after
    AFTER INSERT ON Borders
FOR EACH ROW
DECLARE Country1 NUMBER(15);
	Country2 NUMBER(15);
BEGIN
	FOR LIGNE IN (SELECT * FROM tempBorders) LOOP 
		SELECT country1,country2 INTO Country1,Country2 
                         FROM Borders;
		IF Country2 = LIGNE.country1 AND Country1 = LIGNE.country2 
		THEN
			DBMS_OUTPUT.PUT_LINE('INSERTION IMPOSSIBLE, le couple ' || LIGNE.country1 || ' , ' || LIGNE.country2 ||' existe déja !');
			DELETE FROM Borders B 
                              WHERE B.country1 = LIGNE.country2 AND B.country2 = LIGNE.country1;
		END IF;
	END LOOP;
	DELETE FROM tempBorders;
END;

Malheureusement, j'ai quand même la fameuse erreur :

Code:

1
2
3
4
 
ORA-04091: la table SYSTEM.BORDERS est en mutation ; le déclencheur ou la fonction ne peut la voir
ORA-06512: à "SYSTEM.CHECK_DOUBLONS_AFTER", ligne 5
ORA-04088: erreur lors d'exécution du déclencheur 'SYSTEM.CHECK_DOUBLONS_AFTER'

Etant donnée que la solution que j'ais mis en oeuvre est expliqué ici j'ai eu peu de mal a comprendre ou est le shmilblick...8O

Si quelqu'un peut m'aider, Merci d'avance.

C'est normal...
Ton trigger AFTER INSERT est aussi de type ligne (FOR EACH ROW) donc il va declencher l'erreur ORA-04091 car dans le lien que je t'ai donné il est écrit ceci:

Citation:

L'erreur ORA-04091 se produit dans chacun des cas suivants :
* Si un déclencheur de niveau ligne (qu'il soit BEFORE ou AFTER) tente d'accéder, même par un SELECT, à une table mutante.

En revanche, il n'y a pas d'erreur ORA-04091 dans les cas suivants :
* Le déclencheur est de niveau instruction (AFTER ou BEFORE) et son exécution n'est pas due à une contrainte DELETE CASCADE.

Apparament tu ne l'as pas tout lu, ou alors tu l'as juste survolé pour pouvoir resoudre ton probleme, mais bon je comprends... :D Essaie ç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
36
37
38
39
40
41
42
43
44
45
46
47
-- la table temporaire
CREATE GLOBAL TEMPORARY TABLE tempBorders AS (
		SELECT * FROM Borders WHERE 0=1);
 
-- trigger before insert sur borders
create or replace
TRIGGER "check_doublons_before"
BEFORE INSERT ON Borders FOR EACH ROW 
BEGIN
  INSERT INTO tempBorders
	VALUES (:new.country1, :new.country2, :new.distance);
  EXCEPTION
     when OTHERS THEN
      dbms_output.put_line('Erreur dans le trigger CHECK_DOUBLONS_BEFORE: ' || SQLERRM);
END;
 
 
-- trigger after insert sur borders
create or replace
TRIGGER "check_doublons_after"
AFTER INSERT ON Borders 
DECLARE
nbre NUMBER DEFAULT 0;
BEGIN
 -- pour chaque ligne de la table temporaire, on verifie si le couple existe
 FOR ligne IN (SELECT * FROM tempBorders) LOOP
   SELECT count (*) INTO nbre
   FROM Borders 
   WHERE country1 IN (ligne.country1, ligne.country2)
   AND country2 IN (ligne.country1, ligne.country2);
 
   IF (nbre != 1) THEN
      dbms_output.put_line('Insertion impossible le couple (' || ligne.country1 || ', ' ||
         ligne.country2 || ') existe déjà');
      -- le couple existe, alors on supprime dans la table borders
      DELETE FROM Borders 
      WHERE country1 = ligne.country1
      AND country2 = ligne.country2;
   END IF;
  END LOOP;
 
  DELETE FROM tempBorders;
 
  EXCEPTION
     when OTHERS THEN
      dbms_output.put_line('Erreur dans le trigger CHECK_DOUBLONS_AFTER: ' || SQLERRM);
END;

Chaque fois quand je touche l'erreur de la table mutante je me dit que je suis bien dans les choux et je cherche une autre solution

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
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as mni
 
 
SQL> 
SQL> create table border (
  2    border_key number,
  3    country1   varchar2(2),
  4    country2   varchar2(2),
  5    dist       number
  6  )
  7  /
 
Table created
 
SQL> alter table border
  2    add constraint pk_border Primary Key (country1, country2)
  3  /
 
Table altered
 
SQL> alter table border
  2    add constraint uk_border Unique (border_key)
  3  /
 
Table altered
 
SQL> Create Or Replace Trigger bi_border_fer
  2  Before Insert On Border
  3  For Each Row
  4  Begin
  5    :new.border_key := dbms_utility.get_hash_value(:new.country1,1000,2048) +
  6                       dbms_utility.get_hash_value(:new.country2,1000,2048);
  7  End;
  8  /
 
Trigger created
 
SQL> insert into border (country1, country2, dist)
  2         values('FR','BE', 1200)
  3  /
 
1 row inserted
 
SQL> insert into border (country1, country2, dist)
  2         values('BE','FR', 1200)
  3  /
 
insert into border (country1, country2, dist)
       values('BE','FR', 1200)
 
ORA-00001: violation de contrainte unique (MNI.UK_BORDER)
 
SQL>

Citation:

Envoyé par mnitu

...

Code:

1
2
3
4
5
 
...
  5    :new.border_key := dbms_utility.get_hash_value(:new.country1,1000,2048) +
  6                       dbms_utility.get_hash_value(:new.country2,1000,2048);
...

C'est une fausse bonne idée :aie:

Donc plutôt ç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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as mni
 
 
SQL> 
SQL> CREATE TABLE border (
  2    country1   varchar2(2),
  3    country2   varchar2(2),
  4    dist       number
  5  )
  6  /
 
Table created
 
SQL> CREATE OR REPLACE Function Uk_Country (
  2    p_c1 In Varchar2,
  3    p_c2 In Varchar2
  4  ) Return Varchar2
  5  DETERMINISTIC
  6  Is
  7  Begin
  8    If p_c2 < p_c1 Then
  9      Return p_c2||p_c1;
 10    Else
 11      Return p_c1||p_c2;
 12    End If;
 13  End;
 14  /
 
Function created
 
SQL> Create Unique index ix_border On border (Uk_Country(country1, country2))
  2  /
 
Index created
 
SQL> INSERT INTO border (country1, country2, dist)
  2       VALUES('FR','BE', 1200)
  3  /
 
1 row inserted
 
SQL> INSERT INTO border (country1, country2, dist)
  2       VALUES('BE','FR', 1200)
  3  /
 
INSERT INTO border (country1, country2, dist)
     VALUES('BE','FR', 1200)
 
ORA-00001: violation de contrainte unique (MNI.IX_BORDER)
 
SQL>

17/10/2008, 14h45
skuatamad

Oui la somme était une mauvaise idée, sinon y avait il aussi des risques de collision avec dbms_utility.get_hash_value ?

Par contre pour ta fonction je rajouterais bien p_c2||'___'||p_c1 ou toute chaîne considérée comme totalement impossible, histoire d'être complètement sûr :)
17/10/2008, 17h18
rvfranck
Citation:
Envoyé par mnitu

Donc plutôt ça

Code:

1 2 3 4 5 6 7 ... SQL> Create Unique index ix_border On border (Uk_Country(country1, country2)) 2 / Index created ...
Bien vu, je n'y avais pas pensé. Plus simple et plus rapide. ;)