Trigger Oracle remplacant un INSERT par un UPDATE

Version imprimable

26/01/2009, 13h59
sat83

Trigger Oracle remplacant un INSERT par un UPDATE
Bonjour, j'ai un programme qui réalise des INSERT dans une table.

Je souhaiterais (si possible) créer un Trigger Oracle qui, avant d'effectuer l'INSERT, vérifie l'existence d'un enregistrement semblable dans la base et auquel cas réalise un UPDATE de cet enregistrement plutôt qu'un INSERT dans la base.

Je vais tenter d'expliquer ce que je veut faire a l'aide d'un exemple bateau :
Code:

1 2 3 4 5 6 7 8 9 ----------------- PERSONNE ----------------- ID_PERSONNE (PK de la table ) NOM PRENOM DATE_NAISS VILLE TAILLE
Lorsque j'effectue une requete INSERT comme :
Code:

1 2 INSERT INTO PERSONNE ( ID_PERSONNE, NOM, PRENOM, DATE_NAISS, VILLE, TAILLE) VALUES( 758, 'DUPOND', 'MARC', '10/05/1978', 'PARIS', 80 )
Imaginons qu'il existe un enregistrement dans la table :

Code:

123 | DUPOND | MARC | 10/05/1978 | MARSEILLE | 75

Je souhaiterais que lors de l'INSERT, mon TRIGGER trouve l'enregistrement comportant les même NOM, PRENOM, et DATE_NAISS, et que si un enregistrement existe, plutot que de faire l'INSERT prévu, il réalise un UPDATE de l'enregistrement existant, qui deviendra donc :

Code:

123 | DUPOND | MARC | 10/05/1978 | PARIS| 80

Donc ma question est : est-ce-que ca vous semble possible?

J'espere avoir été clair dans ma requête, si ce n'est pas le cas n'hésiter pas a me le signaler!

Merci a ceux qui pourront m'aider!
26/01/2009, 14h04
mnitu

Nul besoin d'un trigger quand on dispose d'une instruction: MERGE.
26/01/2009, 14h33
fatsora

Bonjour ,
Ca depend ... de la version Oracle

comme aparemment il y a encore des entreprises qui utilisent oracle 6 ici , mieux
vaut se mefier
;)
26/01/2009, 14h33
SheikYerbouti

Citation:

Envoyé par mnitu

Nul besoin d'un trigger quand on dispose d'une instruction: MERGE.

Seulement voilà, le demandeur a juste oublié d'indiquer la version de la base qu'il utilise...
26/01/2009, 14h40
sat83

J'utilise Oracle 9, donc l'instruction MERGE existe bien.
Je ne connaissais pas cette instruction, qui semble effectivement correspondre a ce que je souhaite faire!

Par contre, je dois être un boulet mais j'arrive pas a l'utiliser dans mon cas!

Après un bon petit triturage de cerveau, il semblerai que je commence a m'approcher du but!

Voilà l'instruction a laquelle je suis arrivé (pour illustrer l'exemple de ma première question) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 MERGE INTO PERSONNE T1 USING (SELECT * FROM PERSONNE where nom = 'DUPOND' and prenom='MARC' and date_naiss='10/05/1978') T2 ON ( T1.nom = t2.nom and T1.prenom = t2.prenom and T1.date_naiss = t1.date_naiss ) -- Condition de correspondance WHEN MATCHED THEN -- Si Vraie UPDATE SET VILLE= 'PARIS', poids = 80 WHEN NOT MATCHED THEN -- Si faux INSERT ( ID_PERSONNE, NOM, PRENOM,DATE_NAISS, VILLE, TAILLE ) VALUES ( 758, 'DUPOND', 'MARC', '10/05/1978', 'PARIS',80 );
Cette instruction fonctionne bien dans le cas de l'UPDATE (si un enregistrement existe) par contre l'INSERT ne s'effectue pas si aucun enregistrement existe.

Je dois passer a coté de quelque chose, mais je n'arrive pas a identifier quoi!

Avez-vous une idée?

26/01/2009, 15h16
fatsora

voici des exemples compatibles 9I

http://www.idevelopment.info/data/Or...L/SQL_14.shtml

http://www.oracle-base.com/articles/...Features9i.php

FYI

Citation:
Envoyé par sat83
Après un bon petit triturage de cerveau, il semblerai que je commence a m'approcher du but!

Voilà l'instruction a laquelle je suis arrivé (pour illustrer l'exemple de ma première question) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 MERGE INTO PERSONNE T1 USING (SELECT * FROM PERSONNE where nom = 'DUPOND' and prenom='MARC' and date_naiss='10/05/1978') T2 ON ( T1.nom = t2.nom and T1.prenom = t2.prenom and T1.date_naiss = t1.date_naiss ) -- Condition de correspondance WHEN MATCHED THEN -- Si Vraie UPDATE SET VILLE= 'PARIS', poids = 80 WHEN NOT MATCHED THEN -- Si faux INSERT ( ID_PERSONNE, NOM, PRENOM,DATE_NAISS, VILLE, TAILLE ) VALUES ( 758, 'DUPOND', 'MARC', '10/05/1978', 'PARIS',80 );
Cette instruction fonctionne bien dans le cas de l'UPDATE (si un enregistrement existe) par contre l'INSERT ne s'effectue pas si aucun enregistrement existe.

Je dois passer a coté de quelque chose a mon avis!

Avez-vous une idée?
tu fais 2 fois T1.date_naiss
Code:

1 2 3 T1.nom = t2.nom AND T1.prenom = t2.prenom AND T1.date_naiss = t1.date_naiss
c'est T1.date_naiss = t2.date_naiss

Citation:

tu fais 2 fois T1.date_naiss

Effectivement, petite boulette de ma part!

Mais malheureusement ca ne change rien!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE INTO PERSONNE T1
  USING (SELECT * FROM PERSONNE WHERE nom = 'DUPOND' AND prenom='MARC' AND date_naiss='10/05/1978') T2
    ON ( 
          T1.nom         = T2.nom AND
          T1.prenom      = T2.prenom  AND
          T1.date_naiss  = T2.date_naiss
       ) -- Condition de correspondance
WHEN MATCHED THEN -- Si Vraie
  UPDATE SET VILLE= 'PARIS',  poids = 80
 
WHEN NOT MATCHED THEN -- Si faux
  INSERT ( ID_PERSONNE, NOM, PRENOM,DATE_NAISS, VILLE, TAILLE )
 VALUES ( 758, 'DUPOND', 'MARC', '10/05/1978', 'PARIS',80 );

try this

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE INTO PERSONNE T1
  USING (select 758 ID_PERSONNE , 'DUPOND' NOM , 'MARC' PRENOM , '10/05/1978' DATE_NAISS, 'PARIS' VILLE ,80 taille from dual) s
    ON ( 
          T1.nom         = s.nom AND
          T1.prenom      = s.prenom  AND
          T1.date_naiss  = s.date_naiss
       ) -- Condition de correspondance
WHEN MATCHED THEN -- Si Vraie
  UPDATE SET VILLE= 'PARIS',  taille = 80             
WHEN NOT MATCHED THEN -- Si faux
  INSERT ( ID_PERSONNE, NOM, PRENOM,DATE_NAISS, VILLE, TAILLE )
 VALUES ( 758, 'DUPOND', 'MARC', to_date('10/05/1978','DD/MM/YYYY'), 'PARIS',80 )
/

Effectivement, ca marche (j'ai simplement enlevé les champs inutiles dans le USING) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 MERGE INTO PERSONNE T1 USING (SELECT 'DUPOND' NOM , 'MARC' PRENOM , '01/01/1980' DATE_NAISS FROM dual) s ON ( T1.nom = s.nom AND T1.prenom = s.prenom AND T1.date_naiss = s.date_naiss ) -- Condition de correspondance WHEN MATCHED THEN -- Si Vraie UPDATE SET VILLE= 'LYON', taille = 150 WHEN NOT MATCHED THEN -- Si faux INSERT ( ID_PERSONNE, NOM, PRENOM,DATE_NAISS, VILLE, TAILLE ) VALUES ( 12, 'DUPOND', 'MARC', to_date('01/01/1980','DD/MM/YYYY'), 'LYON',150)
Une autre question : Est ce que cette requête est plutôt rapide, ou plutôt lente a l'exécution? Parce que dans mon cas, je devrais exécuter une 100aine de requête MERGE les unes à la suite des autres (alors que pour le moment j'effectuais des INSERT).

Merci en tout cas pour votre aide a tous.

27/01/2009, 10h12
mnitu

Elle est bien plus rapide qu’un trigger ou un traitement PL/SQL interceptant les exceptions. Mais comme toute instruction SQL c’est bien plus rapide d’exécuter une instruction qui impacte N lignes que de exécuter N fois la même instruction.
27/01/2009, 10h19
sat83

Citation:

Envoyé par mnitu

Elle est bien plus rapide qu’un trigger ou un traitement PL/SQL interceptant les exceptions. Mais comme toute instruction SQL c’est bien plus rapide d’exécuter une instruction qui impacte N lignes que de exécuter N fois la même instruction.

OK, merci de l'info! Malheureusement j'insert dans la base de donnée ce que je lis (ligne à ligne) dans un fichier, donc je suis obligé d'effectuer une requête par ligne.

Merci a tous!
27/01/2009, 10h25
mnitu
Citation:

Envoyé par sat83

OK, merci de l'info! Malheureusement j'insert dans la base de donnée ce que je lis (ligne à ligne) dans un fichier, donc je suis obligé d'effectuer une requête par ligne.

Merci a tous!

Pas toujours. Un fichier text peut être lu comme une tables externe (à partir de Oracle 9i :oops:). Ensuite ton code deviendra:
Code:

1 2 3 4 5 6 Merge Into ma_table (Select ... From ma_table_externe_qui_est_un_fichier ) etc...
27/01/2009, 10h45
sat83

Dans mon cas le fichier a une syntaxe très très particulière (on est loin d'un CSV ou truc du genre), et j'ai donc créer mes propres routines pour parser et lire le fichier.

En gros j'aurais entre 100 et 1000 MERGE a effectuer les uns derrière les autres tous les 1/4 d'heure. Donc même si ca prend quelques secondes ou quelques dizaines de secondes ca devrait me convenir.
28/01/2009, 08h57
sat83

Encore une question sur le MERGE :

Y'a t'il un moyen de savoir par quelle "branche (INSERT ou UPDATE) le MERGE est passé?

Y'a t'il moyen de récupérer le nombre de ligne ajoutées/modifiées?

Merci!
28/01/2009, 09h46
mnitu

Citation:

Envoyé par sat83

Encore une question sur le MERGE :

Y'a t'il un moyen de savoir par quelle "branche (INSERT ou UPDATE) le MERGE est passé?

Je ne pense pas sauf si tu ajoute une colonne dans la table à modifier/insérer.

Citation:

Envoyé par sat83

Y'a t'il moyen de récupérer le nombre de ligne ajoutées/modifiées?

Merci!

SQL%RowCount
30/01/2009, 12h30
sat83

Citation:

SQL%RowCount

Comment le récupérer via une requête SQL? J'ai essayé avec SQL%RowCount mais je n'y suis pas parvenu.

Sql%RowCount en PL/SQL plus précisement

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> set serveroutput on
SQL> 
SQL> Begin
  2  MERGE INTO Emp T1
  3    USING (SELECT *
  4             FROM Emp1
  5          ) t2
  6      ON (
  7            T1.Ename       = T2.ename
  8        AND T1.Job         = T2.job
  9        AND T1.Hiredate    = T2.Hiredate
 10         ) -- Condition de correspondance
 11  WHEN MATCHED THEN -- Si Vraie
 12    UPDATE SET t1.comm = t2.comm
 13  WHEN NOT MATCHED THEN -- Si faux
 14    INSERT (t1.empno, t1.ename, t1.job, t1.mgr, t1.hiredate, t1.sal, t1.comm, t1.deptno)
 15     VALUES (t2.empno, t2.ename, t2.job, t2.mgr, t2.hiredate, t2.sal, t2.comm, t2.deptno );
 16    Dbms_Output.put_line('Nb ligne inserées/modifiées ='||to_char(sql%rowcount));
 17  End;
 18  /
 
Nb ligne inserées/modifiées =1
 
PL/SQL procedure successfully completed