Discussion :

[gapse]

Bonjour,

J'ai sur ma base les tables:
test1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
create table test1(
id number(9) not null,
adresse varchar2(10),
constraint test1_pk primary key (id));

test2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
create table test2(
id number(9) not null,
nom varchar2(10),
test1_id number(9),
constraint test2_pk primary key (id),
constraint test2_fk foreign key (test1_id) references test1 (id));

et la vue:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
create view vue_test2 as select T.nom, T.test1_id, C.adresse, T.id from test1 C, test2 T
where T.test1_id=C.id;

Les clés primaires de mes tables sont auto-incrémentées et je souhaite lors d'un insert sur la vue que les tables test1 et test2 soient mises à jour.

J'ai donc fait:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace trigger tr_test1 instead of insert on vue_test2
begin
    insert into test1
        (id, adresse)
            values(
                :new.test1_id,
                :new.adresse);
    insert into test2
        (id, nom, prenom, test1_id)
            values(
                :new.id,
                :new.nom,
                :new.prenom,
                :new.test1_id);
end;

puis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 insert into vue_test2 (nom,prenom, test1_id, id, adresse) values ('ss','zz',9, 9, 'lille')

Avec test1_id=9 et id=9 n'existant pas encore.

Ca marche mais j'aimerais ne pas avoir à préciser l'id de la table test2 et test1, et que lorsque l'adresse existe déja dans la table test1 aucune ligne ne soit rajoutée.
Pb je n'y arrive pas.

Si vous avez des idées pouvant m'aider ou besoin de plus de précisions...
Je précise que je peux changer la vue (enlever colonne id,...) si nécessaire.

Merci

[plaineR]

1. Tu crées une séquence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create sequence maSeq;

2. Tu crées ton trigger de la manière suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create or replace trigger tr_test1 instead of insert on vue_test2
declare
  v_id number;
begin
    -- Tu récupères le prochain numéro libre de ta séquence
    select  maSeq.nextval into v_id from dual;
    -- Tu insères avec l'id récupéré
    insert into test1
        (id, adresse)
            values(
                v_id,
                :new.adresse);
    insert into test2
        (id, nom, prenom, test1_id)
            values(
                v_id,
                :new.nom,
                :new.prenom,
                :new.test1_id);
end;

[orafrance]

la recherche c'est magique

[gapse]

Merci de ta réponse,

J'avais déjà créé la séquence et le trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create sequence test1_id;
create trigger test1_seq_t before insert on test1
for each row
when (new.id is null)
begin
 select test1_id.nextval into :new.id from dual;
end;
 
create sequence test2_id;
create trigger test2_seq_t before insert on test2
for each row
when (new.id is null)
begin
 select test2_id.nextval into :new.id from dual;
end;

J'ai don fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 tr_test1 instead of insert on vue_test2
declare
 
  v2_id number;
begin
    -- Tu récupères le prochain numéro libre de ta séquence
    select test2_id.nextval into v2_id from dual;
 
    -- Tu insères avec l'id récupéré
    insert into test1
        (id, adresse)
            values(
                :new.test1_id,
                :new.adresse);
    insert into test2
        (id, nom, prenom, test1_id)
            values(
                v2_id,
                :new.nom,
                :new.prenom,
                :new.test1_id);
end;

et j'ai l'erreur 10:15:47 [INSERT - 0 row(s), 0.010 secs] [Error Code: 1, SQL State: 23000] ORA-00001: violation de contrainte unique (EXO_ISYS.TEST2_PK)
ORA-06512: à "EXO_ISYS.TR_TEST1", ligne 14
ORA-04088: erreur lors d'exécution du déclencheur 'EXO_ISYS.TR_TEST1'
... 1 statement(s) executed, 0 row(s) affected, database exec time 0.010 sec [0 successful, 0 warnings, 1 errors]

Est ce que je dois recréer une séquence et la faire démarrer d'où en était l'autre?

[plaineR]

SI tu as déjà des données dans ta table, il faut que ta séquence commence à la plus grande valeur + 1

[remi4444]

J'ai pas saisi le problème de la même façon, du coup je milite activement pour le RETURNING qui est plus adapté car ça sépare bien les processus en laissant les tables se générer toute seule leur clef.

Si tes tables sont vraiment auto-incrémentées de manière indépendantes(triggers before-insert je suppose), pas besoin de préciser les id de clef primaire. Cependant il faut que tu récupère la valeur générée dans la 1iere table pour la mettre dans la 2ieme (si j'ai bien saisi le problème...)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
create or replace trigger tr_test1 instead of insert on vue_test2
declare 
v_id number;
begin
    insert into test1
        (iadresse)
            values(
                :new.adresse) 
      RETURNING id into v_id;
    insert into test2
        (nom, prenom, test1_id)
            values(
                :new.nom,
                :new.prenom,
                v_id);
end;

[gapse]

merci à tous les deux,
Il semblerait que la solution de remi4444 fonctionne.
Celle de plaineR devrait également marcher mais il faudrait créer un séquence supplémentaire à celles déjà créées pour les clés auto-incrémentées (ou je n'ai pas compris).

Si vous avez une idée sur comment faire en sorte que si l'adresse ds test1 existe déjà, aucune ligne dans la table test1 ne soit rajoutée et que la colonne test1_id de la table test2 référence l'adresse en question...

Mais bon je confesse ne pas encore avoir cherché sur ce problème, donc c'est vraiment si vous avez le temps et que vous savez déjà comment faire.

[remi4444]

l'adresse ds test1 existe déjà, aucune ligne dans la table test1 ne soit rajoutée et que la colonne test1_id de la table test2 référence l'adresse en question..

Attention, c'est fonctionnellement que le terrain est glissant là à mon avis tu vas avoir à traiter des cas à la c.. noix:
- si l'adresse existe mais formulée différement (majuscule, espace etc...)
- si l'adresse existe 2 fois (quel id prendre dans ces cas là)

enfin en prenant l'hypothèse que l'adresse existe une fois ou pas du tout, il y a 36 manières de faire mais je verrais bien un truc du genre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
create or replace trigger tr_test1 instead of insert on vue_test2
declare 
v_id number;
begin
 
  BEGIN 
  select adresse into v_id 
     from test1 where adresse = :new.adresse;
  EXCEPTION WHEN NO_DATA_FOUND THEN
  insert into test1
        (adresse)
            values(
                :new.adresse) 
      RETURNING id into v_id;
  END;
 
   insert into test2
        (nom, prenom, test1_id)
            values(
                :new.nom,
                :new.prenom,
                v_id);
end;

Il ne faudra pas oublier de mettre un index sur ta colonne adresse si ta table doit être grosse.

[gapse]

Merci beaucoup ta solution marche. J'ai en effet que le cas où ça existe une fois ou 0., car j'ai déjà éliminé les doublons.
Me reste à indexer les colonnes car c'est effectivement long.

Moi j'étais parti sur une recherche avec if et else mais c'était pas au point.

Encore merci.

[gapse]

Bonjour,

Je reprend les postes precedents car j'ai un nouveau problême sur cet exemple.

Je veux maintenant que si une adresse de la table TEST1 n'existe pas, un message d'erreur prevenant de l'abscence de cette adresse soit ajouté lors d'une l'insertion sur la vue vue_test2 si effectivement cette adresse n'existe pas (au lieu de la rajouter comme le proposait la solution de remi4444.

J'ai donc créer une table de message d'erreur et fait le code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create or replace trigger tr_test1 instead of insert on vue_test2
declare
v1_id number;
v_message varchar2(19);
 
begin
 
    BEGIN
    select id into v1_id from test1
        where adresse=:new.adresse;
    EXCEPTION WHEN NO_DATA_FOUND THEN 
    BEGIN
    SELECT libelle INTO v_message FROM message_err
    WHERE message_err.id=1;
    RETURN(v_message) ; 
    END;
    END;
 
      insert into test2 (nom, prenom, test1_id)
        values(
            :new.nom,
            :new.prenom,
            v1_id);
end;

Pb lors de l'insertion d'une ligne sur la vue : erreur de declencheur, echec de revalidation.

Erreur même lorsque j'insère une ligne avec une adresse existante. Pourtant dans ce dernier cas je ne devrais pas rentrer dans la clause EXEPTION NO_DATA_FOUND ?

Si j'enlève cette clause et insère une ligne avec une adresse existante, ça marche.

J'espère avoir été clair.

Si vous pouvez m'éclairer...
Merci