Vous n'êtes pas identifié(e).

Pages : 1

#1 Re : PL/pgSQL » Trigger générique historisation sur delete » 19/01/2015 13:36:04

guirom

Bonjour,

Je n'ai pas réussi à trouver la méthode pour historiser de manière spécifique chaque table de données. C'est à dire enregistrer les transactions dans une table historique de même structure que la table d'origine par une procédure (déclenchée par un trigger) qui lirait dans les tables de métadonnées la liste des colonnes de la table source pour archiver la transaction dans sa table d'historisation.

J'ai donc adopté la méthode que vous avez mise en place afin d'avoir au moins une vue générale, même s'il parait plus compliqué de restaurer des informations malencontreusement supprimées/modifiées par des erreurs de manipulation de nos utilisateurs SIG. Je l'ai adapté en isolant dans des champs particuliers, les informations géométriques, qui sont présentes dans toutes les tables.

Merci pour votre aide.

Guillaume

#2 Re : PL/pgSQL » Trigger générique historisation sur delete » 26/12/2014 11:08:57

guirom

Sous postgre, je suis donc bloqué dans le traitement que je veux faire ?

En imaginant que les structures des tables d'origine et histo soient toujours identiques, je pourrais utiliser une fonction plus simple, sans boucle, comme celle ci :

$BODY$DECLARE 
  nom_tab VARCHAR;
BEGIN
	nom_tab := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_histo';
	EXECUTE 'INSERT INTO '||nom_tab||' SELECT '||OLD.*;
 RETURN OLD; 
END;
$BODY$

Je suis alors obligé d'utiliser un EXECUTE pour pouvoir inscrire ma ligne dans une table histo dont le nom est dans une variable.
Mais sans construire la liste des colonnes à partir d'une boucle, je ne peux utiliser de fonction quote_ident sur des colonnes que je n'aurais pas identifié, comme le old.*

La "solution" est-elle de créer une fonction par table à historiser pour pouvoir mettre le nom de la table en dur et sortir de l'EXECUTE ?
C'est à dire 350 fois la même fonction pour seulement changer le nom de la table histo ?
Comme ceci :

$BODY$DECLARE 
  nom_tab VARCHAR;
BEGIN
	-- nom_tab := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_histo';
	INSERT INTO toto1_histo SELECT OLD.*;
 RETURN OLD; 
END;
$BODY$

#3 Re : PL/pgSQL » Trigger générique historisation sur delete » 24/12/2014 11:13:34

guirom

Oui, puisque sous Oracle la fonction est dans le trigger généré.
A priori sous postgresql le trigger (on delete dans mon cas) appelle une fonction qui doit donc être générique pour pouvoir etre utilisée sur plusieurs tables.
Ce que je cherche à faire : lorsqu'il y a une suppression d'un enregistrement dans la table toto, inscrire l'enregistrement dans la table toto_histo.
Et ce, pour un ensemble de tables.
J'ai donc besoin d'une fonction qui liste les colonnes de la table impactée par le trigger et qui insère les valeurs supprimées ("OLD") dans la table histo correspondant.

Voici la fonction que j'ai mis en place :

CREATE OR REPLACE FUNCTION public.ftr_maj_tabhisto()
  RETURNS trigger AS
$BODY$DECLARE
nb integer;
vcol record;
nomcol1 varchar;
nomcol2 varchar;
BEGIN
    nb := 0;
    for vcol in (select column_name FROM information_schema.columns where table_name=TG_TABLE_NAME) loop
        nb := nb+1;
        if nb = 1 then
          nomcol2 := ':old.'||vcol.column_name;
          nomcol1 := vcol.column_name;
        else
          nomcol2 := nomcol2 ||',:old.'||vcol.column_name;
          nomcol1 := nomcol1 ||','||vcol.column_name;
        end if;
    end loop;
    EXECUTE 'insert into '||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_HISTO ('||nomcol1||') values ('||nomcol2||')';
RETURN OLD;
END;
$BODY$

Et voici l'erreur retournée à la suppression d'un enregistrement :

ERREUR:  entrée manquante de la clause FROM pour la table « old »
LINE 1: ...,source_donnee,adresse,log_integ,log_maj) values (old.object...
                                                             ^
QUERY:  insert into administratif.erp_HISTO (objectid,erp,nom,code_insee_commune,nom_commune,date_integ,date_maj,shape,date_donnee,source_donnee,adresse,log_integ,log_maj) values (old.objectid,old.erp,old.nom,old.code_insee_commune,old.nom_commune,old.date_integ,old.date_maj,old.shape,old.date_donnee,old.source_donnee,old.adresse,old.log_integ,old.log_maj)
CONTEXT:  PL/pgSQL function ftr_maj_tabhisto() line 27 at instruction EXECUTE

********** Erreur **********

ERREUR: entrée manquante de la clause FROM pour la table « old »
État SQL :42P01
Contexte : PL/pgSQL function ftr_maj_tabhisto() line 27 at instruction EXECUTE

Je n'arrive pas à utiliser les valeurs OLD dans l'insertion à travers la commande EXECUTE.
Merci

#4 PL/pgSQL » Trigger générique historisation sur delete » 23/12/2014 18:21:26

guirom
Réponses : 7

Bonjour,
Migrant notre SGBDR de Oracle vers Postgre, je fais appel à votre savoir devant une problématique que je n'arrive pas à résoudre.
Nous historisons toutes nos tables avec une procédure de génération de triggers on delete qui copie l'enregistrement supprimé dans la table d'historisation (table_origine||'_histo'). Je ne connais ni l'ensemble des tables, ni l'ensemble des colonnes associées à chaque table.

La procédure cherche donc la liste des tables, puis pour chaque table une boucle sur la table système des colonnes, qui construit le script a exécuter pour créer les triggers nécessaires sur chaque table.

Voici mon script, qui fonctionne bien sous oracle.

CREATE OR REPLACE PROCEDURE ADM."CREATE_TRIG_D"
as
---------------------------------------------------------------------------------------------
-- Fonction permettant de créer les triggers d'historisation
-- Sur delete d'un enregistrement dans une table utilisateur
---------------------------------------------------------------------------------------------
col_exist exception;
pragma exception_init(col_exist,-01430);
sqltxt varchar2(3000);
nb number(5);
nomcol1 varchar2(3000);
nomcol2 varchar2(3000);
cursor c_object is
    select table_name
    from user_tables
    where TABLE_NAME not like '%HISTO';
    V_object c_object%rowtype;
begin
    for v_object in c_object
    loop
                 nb := 0;
             for vcol in (select column_name from user_tab_columns where table_name=v_object.table_name) loop
                  nb := nb+1;
               if nb = 1 then
                     nomcol2 := ':old.'||vcol.column_name;
                  nomcol1 := vcol.column_name;
               else
                     nomcol2 := nomcol2 ||',:old.'||vcol.column_name;
                  nomcol1 := nomcol1 ||','||vcol.column_name;
               end if;
           end loop;
        begin
            sqltxt := 'CREATE OR REPLACE TRIGGER ADM.TR_'||v_object.table_name ||'_D after delete on SIG.'||v_object.table_name ||' for each row begin insert into ADM.'||v_object.table_name ||'_HISTO ('||nomcol1||') values ('||nomcol2||'); end;';
            execute immediate sqltxt;
        end;
    end loop;
end;


Je n'arrive pas à reproduire le principe sous postgre, de manière à avoir une fonction trigger générique sur laquelle s'appuieraient les triggers on delete.
Voici un bout de code sur lequel je suis pour l'instant, mais selon la methodo, soit les variables ne sont pas reconnues, soit les 'old. ou new.' ne sont pas reconnues (en EXECUTE par exemple)

CREATE OR REPLACE FUNCTION public.ftr_maj_tabhisto()
  RETURNS trigger AS
$BODY$DECLARE
nb integer;
vcol record;
nomcol1 varchar;
nomcol2 varchar;
BEGIN
    nb := 0;
    for vcol in (select column_name FROM information_schema.columns where table_name=TG_TABLE_NAME) loop
        nb := nb+1;
        if nb = 1 then
          nomcol2 := ':old.'||vcol.column_name;
          nomcol1 := vcol.column_name;
        else
          nomcol2 := nomcol2 ||',:old.'||vcol.column_name;
          nomcol1 := nomcol1 ||','||vcol.column_name;
        end if;
    end loop;
    EXECUTE 'insert into '||TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME||'_HISTO ('||nomcol1||') values ('||nomcol2||')';
RETURN OLD;
END;
$BODY$

Merci d'avance de votre aide et bonnes fetes de fin d'année

Guillaume

Pages : 1

Pied de page des forums

Propulsé par FluxBB