Discussion :

[bvincent69100]

Bonjour à tous.

Après des recherches infructueuses je m'adresse à vous.

Tout d'abors je travail avec Oracle 10g XE.

J'aimerai créer un trigger qui à chaque INSERT/UPDATE/DELETE écrive dans un fichier les champs de l'enregistrement concerné.

Etant un novice d'Oracle voici ce que j'aimerai savoir:

- Faut il que je crée trois trigger (un pour insert, un pour update, un pour delete), ou puis je le faire en un seul;
- Quelle est la commande ou ps qui me permette d'écrire dans un fichier.

Pour l'instant j'ai:

Code :

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

1
2
3
4
5
6
7
 
CREATE TRIGGER TRG_DELETE_ON_MA_TABLE
AFTER DELETE ON MA_TABLE
;
; Ecrire les valeur de l'enregistrement concerné (sans un fichier texte plat, genre .csv)
;
END;

Merci de votre aide.

Ben.

[shan08]

tu peux faire le traitement dans un seul trigger
exp

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
create or replace TRIGGER TRG_DELETE_ON_MA_TABLE
  AFTER INSERT OR DELETE  OR UPDATE ON Ma_table
DECLARE
...
BEGIN
if deleting then
     ....;
elsif inserting then
    .....;
else --updating
  .........;
END IF;
END;

[mnitu]

Dans un trigger, toute action qui n’est pas transactionnelle, comme l’écriture dans un fichier par exemple, est une très mauvaise pratique de programmation. Vous devez envisager une autre solution.

[bvincent69100]

Ok mnitu, par contre d'après les spécifications techniques je n'ai pas le choix.

Pour que tu comprenne mieux, on met en place un bus de données (ESB) qui synchronise différents flux (différentes bdd, oracle pour ma part), et l'échange se fait via des fichiers plats (csv ou xml par exemple).

Donc je comprend que cela n'est pas recommandé, mais est-il possible de le faire, ou alors quelle serai la solution (sans passer par les trigger) ?

Merci.

Ben.

[mnitu]

Les triggers sont des animaux un peu bizarres dans la ménagerie d’Oracle. Parfois, dans des conditions bien identifiés, un trigger peut se ré déclencher. Tant que l’action du trigger est transactionnelle, insert dans une table pour exemple, cella n’est pas grave, parce que Oracle ferra le ménage nécessaire via le rollback, avant de se re déclencher. Mais l’écriture dans un fichier n’est pas transactionnelle et donc, dans ce cas, il se peut qu’il y ait des doublons dans le fichier. Si vous pensez que cella n’est pas grave allez y.

J’ai du mal à vous conseille avec seulement les informations que vous me donnez. Vous avez besoin de créer un fichier ASCII ça ne pose aucune problème. Mais la question est qu’est-ce que déclenche votre traitement ? Pour quoi voulez-vous faire ce traitement dans un trigger ?

[McM]

C'est clair que le faire par trigger c'est la mauvaise solution.
A chaque modif (insert/upd/del), il va falloir ouvrir le fichier, écrire les données, fermer le fichier... Ca va ramer.

Si tu dois exporter des données en fichiers texte, utilise plutôt une procédure qui va récupérer toutes les données à prendre et créer ton fichier en une seule fois.

Le problème c'est le DELETE que tu n'aura plus.
Le mieux dans ce cas, est de logguer les modifs (ins,del,upd) dans une table spécifique.
Table que tu vas lire pour retrouver les enreg modifiés de ta table principale pour ainsi créer ton fichier

[bvincent69100]

Ok, donc tout le monde est daccord pour dire que ce que je souhaite est "dégeulasse".

Néanmoins j'aimerai pouvoir le faire =) . Quel est donc la commande que je dois intégrée dans mon trigger pour écrire dans un simple fichier texte.

Mais la question est qu’est-ce que déclenche votre traitement ? Pour quoi voulez-vous faire ce traitement dans un trigger ?

Je vais essayé d'être concis pour que vous compreniez ma problématique:

Situation initiale (AS/IS):

Un site avec un base oracle (site A), un deuxieme site avec une base SQL Server (site B) par exemple. Actuellement le utilisateurs du site B ont besoin des données de la base du site A mais le transfert se fait à la main (via clef usb par exemple).

TO-BE

Notre projet est d'automatiser l'échange de flux grace à un ESB. Donc envoi automatique dudit fichier texte (données extraite de la base oracle) via l'ESB vers le site B (ajout ou mise à jour automatique de la base SQL Server via ce procédé).

J'espére avoir été assez clair quant au pourquoi de ma question.

Merci.

Ben.

[McM]

Utilise UTL_FILE (regarde dans le Tutoriel de Sheikyerbouti celui sur le PL/SQL)

[skuatamad]

Parfois, dans des conditions bien identifiés, un trigger peut se ré déclencher.

C'est surtout le cas pour les triggers before il me semble.
Ce qui ne veut pas dire que j'encourage à bvincent à faire du non transactionnel dans un trigger, mais avec un trigger after c'est peut être moins grave.

Pour ta problématique, es tu sûr d'avoir besoin de générer un fichier à chaque modification ?
Une solution pourrait être d'utiliser un job oracle pour générer ton fichier toutes x minutes.

[Waldar]

Créer une table externe ne serait-il pas envisageable ?

[mnitu]

C'est surtout le cas pour les triggers before il me semble.

After aussi.

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
25
26
27
28
29
30
31
32
33
34
35
 
SQL> create or replace trigger t_trigger after update on t for each row
  2  declare
  3     l_seq number;
  4  begin
  5       select s.nextval into l_seq from dual;
  6  
  7        insert into msg1
  8        (seq, sid, old_id, old_y, new_id, new_y )
  9        values
 10        (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
 11  
 12        log_msg2
 13        (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
 14  end;
 15  /
...
SQL> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
  2          from msg2, msg1
  3     where msg2.seq = msg1.seq(+)
  4     order by msg2.seq
  5  ;
 
DECODE(MSG1.SEQ,NULL,'NOTVISIB                                     SEQ                                     SID                                  OLD_ID                                   OLD_Y                                  NEW_ID                                   NEW_Y
------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
                                                                     1                                18880543                                       3                                       0                                       3                                      -1
NOT VISIBLE                                                          2                                18880577                                       2                                       0                                       2                                       1
                                                                     3                                18880601                                       1                                       0                                       1                                      -1
                                                                     4                                18880601                                       5                                       0                                       5                                      -1
NOT VISIBLE                                                          5                                18880577                                       1                                      -1                                       1                                       1
NOT VISIBLE                                                          6                                18880577                                       2                                       0                                       2                                       1
NOT VISIBLE                                                          7                                18880577                                       3                                      -1                                       3                                       1
NOT VISIBLE                                                          8                                18880577                                       5                                      -1                                       5                                       1
 
8 rows selected

Bref, l'exemple complet se trouve ici.

[skuatamad]

Ok merci mnitu, je connaissais l'exemple qui est avec un trigger before, et j'ai cru (sans rien tester ) qu'avec un after ce serait différent...
Va falloir que je reteste un peu tout ça parce que la lecture c'est pas vraiment suffisant.

[bvincent69100]

Okay donc j'ai potassé tout ce dont vous m'avez parlé et voici ce que j'ai fait. Dite moi ce que vous en pensez et surtout si vous pouvez m'aider sur les deux lignes qu'il me manque (XXX):

CREATE OR REPLACE TRIGGER MON_TRIGGER AFTER INSERT ON Ma_table

Declare
-- Noms des fichiers --
LC$Fic_out Varchar2(128) := 'OUT.TXT' ; -- Nom du fichier de sortie
-- Noms des répertoires --
LC$Dir_out Varchar(30) := 'FLUX_OUT' ; -- Nom du DIRECTORY ou sera écrit le fichier
-- Pointeurs de fichier --
LF$FicOUT UTL_FILE.FILE_TYPE ;
-- Tampon de travail --
LC$Ligne Varchar2(32767) ;
-- Message --
LC$Msg Varchar2(256) ;
-- Exception --
LE$Fin Exception ;

Begin

-- Ouverture du fichier de sortie --

Begin

LF$FicOUT := UTL_FILE.FOPEN( LC$Dir_out, LC$Fic_out, 'W', 32764 ) ;

Exception

When OTHERS Then
LC$Msg := SQLERRM || ' [' || LC$Dir_out || '] -> ' || LC$Fic_out;
Raise LE$Fin ;
End ;


-- Ecriture dans le fichier --

Begin

-- Récupération de l'enregistrement qui a déclenche le trigger--

-- XXX La je ne sais pas !

-- écriture dans le fichier de sortie --
UTL_FILE.PUT_LINE( LF$FicOUT, XXX ) ;

-- Fermeture des fichiers --
UTL_FILE.FCLOSE( LF$FicOUT ) ;

End ;

Exception
When LE$Fin Then
UTL_FILE.FCLOSE_ALL ;
RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
End ;

END;

Merci pour vos avis et propositions de solution.

[McM]

Tu accèdes au données des lignes mises à jour /insérées etc... par :new.nom_colonne (pour les nouvelles données) et :old.nom_colonne (pour les anciennes)
Par contre, il faut que tu mettes ton trigger au niveau ligne (FOR EACH_ROW)

C'est pour ça qu'on dit que ça va ramer : Si tu insères 10 000 lignes, le trigger se déclenchera 10 000 fois.

[bvincent69100]

Ok, en fait je me permet de faire cela car si la base est accéder plus de 10 fois par jour c'es vraiment le bout du monde. C'est une base qui contient des formules chimiques trés peu mises à jour.

Merci pour votre aide, je continue ...

[bvincent69100]

Bon j'arrive à faire ce que je veux en gros, voici mon trigger pour un delete:

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 "TRG_AFTER_DELETE"
AFTER
delete on "COMPOSITION"
for each row
begin
DECLARE
f_out UTL_FILE.FILE_TYPE;
BEGIN
f_out:=UTL_FILE.FOPEN('FLUX_OUT','out.txt','a');
UTL_FILE.PUT(f_out,'La date - DELETE - ');
UTL_FILE.PUT(f_out,'old.IDPRODUIT');
UTL_FILE.FCLOSE(f_out);
END;
end;

Deux problèmes (qui n'en sont pas j'en suis certain):

- Obtenir la date courrante;

- Récupérer la valeur (IDPRODUIT) de la requete que je vient d'executer.

Je cherche en parallele biensur ... =)

Merci.

Ben.

[McM]

la date : SYSDATE

Code :

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

1
2
3
4
f_out:=UTL_FILE.FOPEN('FLUX_OUT','out.txt','a');
UTL_FILE.PUT(f_out, to_char(sysdate, 'DD/MM/RRRR HH24:MI:SS') || ' - DELETE - ');
UTL_FILE.PUT(f_out,to_char(:old.IDPRODUIT) );
UTL_FILE.FCLOSE(f_out);

[bvincent69100]

Merci beaucoup Msieur' !