Forums PostgreSQL.fr

guirom

Membre

Trigger générique historisation sur delete

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

gleu

Administrateur

Re : Trigger générique historisation sur delete

Votre fonction PL/SQL (oracle donc) ajoute des triggers. Votre fonction PL/pgsql (donc postgresql) insère des lignes dans une table. Difficile de comprendre ce que vous cherchez réellement à faire.

Le mieux est de donner votre fonction, et l'erreur que cela génère. Ensuite, nous, on peut expliquer l'erreur et éventuellement proposer une correction. Vous essayez et en cas d'erreur, on recommence.

---

Guillaume.

guirom

Membre

Re : Trigger générique historisation sur delete

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

gleu

Administrateur

Re : Trigger générique historisation sur delete

Les références OLD et NEW ne sont comprises que dans la fonction elle-même. Si vous placez le texte OLD.qqc dans une chaîne de caractère, c'est juste une chaîne de caractère sans signification particulière. Dans ce cas, il vaut mieux utiliser OLD.* après avoir rempli la liste des colonnes.

De plus, votre fonction présente de nombreux problèmes. Si une des colonnes vaut NULL, la requête à exécuter devient NULL, ce qui provoque une erreur. Si une des colonnes contient un texte qui contient une virgule, PostgreSQL aura plus de valeurs que de colonnes. Si jamais une des colonne contient un guillemet simple, la chaîne de caractères représentant la requête à exécuter s'arrêtera avant ce qui est prévu, provoquant d'autres erreurs. Bref, il faudrait songer à utiliser les fonctions quote_ident et quote_literal.

---

Guillaume.

guirom

Membre

Re : Trigger générique historisation sur delete

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$

Dernière modification par guirom (26/12/2014 11:10:36)

gleu

Administrateur

Re : Trigger générique historisation sur delete

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

Quelque soit le logiciel, on est forcément bloqué à un moment. Bref

La documentation http://docs.postgresql.fr/9.4/plpgsql-trigger.html vous explique ce qu'il est possible de faire.

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 ?

Non. D'ailleurs, je ne vois pas pourquoi vous cherchez à créer votre propre fonction d'historisation alors qu'il en existe déjà. E-maj par exemple (http://pgxn.org/dist/e-maj/).

---

Guillaume.

Geo-x

Membre

Re : Trigger générique historisation sur delete

Bonjour Guirom.

J'ai créé une fonction trigger qui permet l'enregistrement des données modifiées dans uen table historique.

Pour cela j'ai commencé par créer la table dans laquelle sont enregistrés les éléments modifiés :

CREATE TABLE historique
(
  ogc_fid serial NOT NULL,
  table_modif character varying(120),
  type_modif character varying(120),
  ogc_fid_modif integer,
  date_modif date,
  heure_modif time without time zone,
  original_data text,
  new_data text,
  CONSTRAINT historique_pkey PRIMARY KEY (ogc_fid )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE historique
  OWNER TO postgres;

Puis j'ai créé la fonction trigger :

CREATE OR REPLACE FUNCTION historique()
  RETURNS trigger AS
$BODY$
DECLARE
    old_data text;
    data_modif text;
BEGIN

    IF (tg_op = 'UPDATE')
	
	THEN
        old_data := ROW(OLD.*);
        data_modif := ROW(NEW.*);
        INSERT INTO historique (table_modif,type_modif,ogc_fid_modif,date_modif,heure_modif,original_data,new_data) 
		VALUES (tg_table_name::text,'Update',NEW.ogc_fid,current_date,current_time,old_data,data_modif);
        RETURN NEW;
		
    ELSIF (tg_op = 'DELETE')
	
	THEN
        old_data := ROW(OLD.*);
	data_modif := NULL;
        INSERT INTO historique (table_modif,type_modif,ogc_fid_modif,date_modif,heure_modif,original_data,new_data) 
		VALUES (tg_table_name::text,'Delete',OLD.ogc_fid,current_date,current_time,old_data,data_modif);
        RETURN OLD;
		
    ELSIF (tg_op = 'INSERT')
	
	THEN
	old_data := NULL;
        data_modif := ROW(NEW.*);
        INSERT INTO historique (table_modif,type_modif,ogc_fid_modif,date_modif,heure_modif,original_data,new_data) 
		VALUES (tg_table_name::text,'Insert',NEW.ogc_fid,current_date,current_time,old_data,data_modif);
        RETURN NEW;
		
    END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Et pour finir, j'applique à chaque table pour laquelle je souhaite enregistrer les modifications le lancement de la fonction trigger :

CREATE TRIGGER historique
  BEFORE INSERT OR UPDATE OR DELETE
  ON table
  FOR EACH ROW
  EXECUTE PROCEDURE historique();

Mais peut-être existe-t-il des méthodes plus simple, que je ne connais pas et pour lesquelles je serais curieux de connaitre l'existence.

Geo-x

guirom

Membre

Re : Trigger générique historisation sur delete

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