Discussion :

[sisqo60]

Bonjour à tous,

Je rencontre un problème pour lequel j'aurais bien besoin de votre aide. Le but est de créer un trigger qui sauvegarde dans une table historique le changement des champs d'une base de données. La base de données pouvant évoluer, il faudrait que cela soit "générique". un exemple :

Table
Personne (ID, Nom, Prenom, Adresse, DateNaissance)

Historique(ID_Historique, ID_personne, Table, Champs, OldValue, NewValue, Utilisateur, DateEvenement)

Voila le schema pour simplifier.

A la modificartion d'un enregistrement de la table personne un trigger sera déclenché pour historiser ce changement. Seuls les champs modifiées feront l'objet d'une insertion dans la table historique. Le but est de créer le trigger sans qu'on ait à le modifier par la suite si l'on ajoute un nouveau champs dans la table personne. J'ai quelques pistes mais je n'arrive pas à cercler le problème.







mes pistes sont:

1. Récupérer les champs dynamiquement à partir de la table système RDB$RELATION_FIELDS
2. comparer la valeur des champs de old et new à l'aide de la liste précédente

j'en ait déduit un code sql à peu près comme celui la
mais ne compile pas :field ne peut pas être utilisé comme ça. Auriez-vous d'autres idées...

Code :

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

1
2
3
4
5
6
7
8
9
 
for select RDB$FIELD_NAME from RDB$RELATION_FIELDS where RDB$RELATION_NAME = 'Personne'
    into :field
  do
  begin
if (old.:field <> new.:field)
    insert into historique
    values(gen_id(gen_histo, 1), new.id, 'Personne',  :field, old.:field, new.:field, current_user, current_timestamp);
  end

Voila je pense vous avoir expliqué le problème et j'espère que vous serez à même de povoir m'aider.
Merci par avance à ceux qui voudront se donner la peine de lire tout ça.

[VLDG]

je ne sais pas si cela fontionne mais tu m'as l'air sur la bonne voie (utilisation des tables systèmes...)

tu peux aussi regarder IBLogManager qui fait ce que tu veux :
http://www.upscene.com/products.audit.iblm_main.php

[sisqo60]

Merci beaucoup VLDG pour ta réponse mais l'outil que tu me proposes est payant et de plus l'essai que je viens de réaliser a échoué, donc je ne retiens pas IBLogManager.
Par contre, j'ai fait des recherches et je pense que je touche presque au but, il me manque cette idée (pour ne pas dire le coup de génie) qui me ferais terminer. J'utilise des execute statment et il y a un problème avec les enregistrements old/new! Je peux pas les executer comme ça, mais j'ai absolument besoin de les récupérer. Auriez vous une solution?
Voici ce que j'obtiens en définitive :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
FOR SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'Personne'
    INTO :FIELD
  do
  begin
  requete = 'select '||:field||' from new';
  EXECUTE statement requete into :nouveau;
  requete = 'select '||:field||' from old';
  EXECUTE statement :requete into :ancien;
   if (ancien <> nouveau) then
    INSERT INTO historique
    VALUES(gen_id(gen_histo, 1), new.id, 'Personne',  :FIELD, ancien, nouveau, current_user, current_timestamp);
  end

[Barbibulle]

Pourquoi faire dans le dynamique ?

Personnellement je me serais orienté pour une solution plus statique. A chaque fois que j'ajoute une colonne je régénérerai le trigger en question.

A moins que vous ayez beaucoup de changement dans la structure de vos tables ???

En plus IBExpert crée automatiquement de tels triggers (Menu outils/Gestion des Logs). Par contre je ne pourrai pas vous dire si cette fonction est disponible dans la version gratuite.

[sisqo60]

en faite je souhaiterais le faire dynamiquement justement pour éviter de recréer à chaque fois les triggers (éviter les oubli...). Les changements de la structure des tables il y en aura certainement mais à mon avis pas beaucoup. C'est plus pour la sérénité et la rapidité que je veux implémenter la création dynamique des triggers, mais si on me dit que c'est concrètement impossible je ne chercherais pas plus loin, mais j'ai bien l'impression que ça doit être réalisable.
Par rapport à IBExpert, nous avons une license site mais il m'affiche ce message d'erreur quand j'essaye de créer la gestion des logs :

Error Message:
----------------------------------------
Zugriffsverletzung bei Adresse 007AE030 in Modul 'IBExpert.exe'. Schreiben von Adresse 0000000C

[makowski]

mais si on me dit que c'est concrètement impossible je ne chercherais pas plus loin

concretement impossible non
concretement improductif surement
avec les execute statement et execute block, le code ne sera pas compilé
en plus cela fait un surcout de traitement
Il serait beacoup plus productif d'ecrire une procédure de test de qualité à faire tourner après chaque changement de structure pour voir si l'on rien oublié
et d'ecrire pour de vrai les triggers

[sisqo60]

Je pensais à ça dans un soucis de cohésion, mais en perdant de vue que les performances seront en effet bien altérées...
Merci Makowski, Barbibulle et VLDG d'avoir pris du temps pour me répondre.
Mais pour des raisons de culture personnelle, pourquoi ne peut-on pas intégrer old/new dans les statments?

[makowski]

Mais pour des raisons de culture personnelle, pourquoi ne peut-on pas intégrer old/new dans les statments?

parce que ce sont des variables liées au contexte et que le execute statement sort de ce contexte

[sisqo60]

bien reçu, merci pour cette précision.

[Barbibulle]

Je pense (à vérifier/confirmer) que Old.XX et new.xx sont des variables d'environnement du contexte, elles n'ont pas d'existence dans les tables systèmes.

Un exécute statement permet de lancer une requête créée dynamiquement pas d'évaluer des variables.

Et même si vous arrivez a accéder dynamiquement à ce contexte vous n'êtes pas au bout de vos peines. Imaginons que cela fonctionne et que vous oubliez ce trigger. Un jour vous avez besoin d'ajouter une colonne d'un type que vous ne pourrez pas caster dans votre log (un blob text par exemple). Votre trigger va planter lors de l'utilisation et non lors de la conception...

Je suis de l'avis de Makowski, faites vous une procédure à suivre après chaque modifications des tables.

Si vous avez la licence IBExpert, essayer de voir pourquoi cela plante car franchement il fait tout ca très bien et en quelques clics vous avez votre log. et les outils d'extraction / export.

[sisqo60]

je vais tout de même regarder du coté de ibexpert...