trigger historisation des modifications sur une base de données

Version imprimable

02/02/2011, 15h22
chris0938

trigger historisation des modifications sur une base de données

Bonjour,
Je souhaiterai mettre en place un trigger qui insert des lignes dans une table d'historique dès qu'un utilisateur fait des modifications sur la base.

J'aimerai pouvoir récupère le nom de la table modifié, l'identifiant de la ligne, l'utilisateur qui a fait cette modification, quel est le type de la modification (update,delete,insert), et la date.
Pour le moment, je n'ai que la date que je sais comment récupère mais le reste je ne sais pas.

Est ce possible de récupère ces informations?

Merci d'avance pour tout informations.
02/02/2011, 17h09
estofilo

Qu'entends-tu par "l'identifiant de la ligne" ? Car par défaut, une ligne de table n'a pas spécialement d'identifiant unique stable. A moins que les tables soient déclarées WITH OIDS mais cet usage est en voie d'obsolescence.
02/02/2011, 17h13
estofilo

Pour les autres données, l'utilisateur s'obtient par la fonction current_user, le nom de la table en argument du trigger avec TG_ARGV, et le nom de l'opération avec TG_OP.
03/02/2011, 09h11
chris0938

Bonjour,
je te remercie de tes réponses.
"Qu'entends-tu par "l'identifiant de la ligne" ?" enfaite je parle de la clé primaire de l'enregistrement qui a été modifié.

De plus, j'aimerai savoir si c'est possible d'utiliser le même trigger et fonction pour 10 tables différentes?

pour faire le trigger je me vais m'appuyer sur cette exemple

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
 
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
    --
    -- Ajoute une ligne dans emp_audit pour refléter l'opération réalisée
    -- sur emp,
    -- utilise la variable spéciale TG_OP pour cette opération.
    --
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
        RETURN NEW;
    END IF;
    RETURN NULL; -- le résultat est ignoré car il s'agit d'un déclencheur AFTER
END;
$emp_audit$ language plpgsql;
 
CREATE TRIGGER emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

et donc ma question est ce possible de mettre sur cette ligne:

Code:

AFTER INSERT OR UPDATE OR DELETE ON emp

une autre table

Code:

AFTER INSERT OR UPDATE OR DELETE ON emp on salarie on ...

et donc la j'utiliserai TG_ARVG pour connaitre quelle la table en question.

je sais pas si c'est bien clair.

03/02/2011, 15h10
estofilo

Non un même trigger ne peut pas être associé à plusieurs tables, mais une même fonction peut être associée à plusieurs triggers.

La difficulté principale pour étendre l'exemple montré à de multiples tables est qu'il déverse dans une unique table d'audit dont la structure est spécifiquement liée à une seule table.

Pour pouvoir insérer dans une table d'audit déterminée dynamiquement il faut utiliser du SQL dynamique avec EXECUTE et le code va être un niveau de difficulté au-dessus.

Pour la clef primaire, il faut voir qu'il n'y a pas d'obligation pour une table d'avoir une clef primaire, et que si elle en a une, une clef primaire peut porter sur de multiples colonnes.
03/02/2011, 15h22
chris0938
D'accord merci pour ces informations.
" mais une même fonction peut être associée à plusieurs triggers." comment une fonction peut être associé a plusieurs trigger étant donnée que dans l'entête on spécifie le nom du trigger?
Code:

1 2 create function histo_audit() returns trigger as $histo$
est ce qu'avec ceux ci TG_Relid je pourrais connaitre correctement l'enregistrement modifié?
03/02/2011, 15h31
estofilo
Citation:
Envoyé par chris0938

D'accord merci pour ces informations.
" mais une même fonction peut être associée à plusieurs triggers." comment une fonction peut être associé a plusieurs trigger étant donnée que dans l'entête on spécifie le nom du trigger?

Code:

1 2 create function histo_audit() returns trigger as $histo$
Non, le nom du trigger n'est pas là, il est dans l'ordre CREATE TRIGGER nom_du_trigger ....

Citation:

est ce qu'avec ceux ci TG_Relid je pourrais connaitre correctement l'enregistrement modifié?

TG_relid permet seulement de connaître la table modifiée. A ce propos je réalise qu'il y a TG_TABLE_NAME et TG_TABLE_SCHEMA comme variables accessibles dans un trigger, donc il vaut mieux utiliser ça plutôt que le mettre soi-même dans TG_ARGV contrairement à ce que je proposais plus haut dans la discussion.
03/02/2011, 15h45
chris0938

Quand on dit

Code:

returns TRIGGER AS $histo$

cela veut bien dire retourné le trigger histo? ou j'ai vraiment mal compris

TG_RELID l'ID de l'objet de la table qui a causé le déclenchement.
TG_RELNAME Type de données nom ; le nom de la table qui a causé le déclenchement.
03/02/2011, 15h56
estofilo

Citation:

Envoyé par chris0938

cela veut bien dire retourné le trigger histo? ou j'ai vraiment mal compris

Non ça veut dire totalement autre chose. $histo$ est une balise syntaxique qui encadre le code qui suit juste après. La valeur pourrait aussi bien être $toto$ ou encore $$, tant que cette valeur ne se trouve pas aussi dans le code lui-même, puisqu'elle sert justement à le délimiter.
03/02/2011, 16h01
chris0938

ok merci pour ces informations.
je vais tester tout cela.

Bonjour,
j'ai mis en place le trigger. j'utilise tg_relname car la version de postgres est la 8.4 donc tg_table_name ne fonctionne pas.
après cette modification le trigger marche dés que je fais un insert,update..
mais la seule chose qui ne fonctionne pas c'est l'id de l'enregistrement modifié.
je m'explique:
Code:

1 2 3 4 5 6 7 8 9 insert into profil (id,libelle,datecreation) values ('PROF649302','gestionnaire de site',now()); INSERT 0 1 select * from histo; id | typemod | datemodif | usermodif | idmodif | tablemodif ---------+-----------+---------------+------------+---------+------------ histo453 | I | 2011-02-07 | postgres | 152476 | profil
idmodif vaut 152476 alors que je voudrai que ca soit prof649302. je ne sais pas si c'est bien possible.

voici la partie de la fonction qui nous intéresse ici
Code:

1 2 3 4 elsif (TG_OP='INSERT') then insert into histo(typemod,datemodif,usermodif,idmodif,tablemodif) select 'I',now(),user,TG_Relid,TG_RELNAME; return new;
merci d'avance pour toutes idées.

07/02/2011, 13h47
chris0938

je viens de trouver.
je dois utiliser new.id ou old.id.

merci
07/02/2011, 17h42
estofilo

Citation:

Envoyé par chris0938

j'utilise tg_relname car la version de postgres est la 8.4 donc tg_table_name ne fonctionne pas.

Ce n'est pas normal car c'est bien disponible en 8.4
08/02/2011, 10h39
chris0938
Bonjour,
je sais pas. j'ai eu ce message quand j'utilisais tg_table_name:
Code:

1 2 Error: column "tg_table_name" does not exist
mais avec tg_relname cela fonctionne donc bon moi ca me va. c'est peut pas aussi bien que tg_table_name mais bon.