Forums PostgreSQL.fr

mich30

Membre

trigger

Bonjour
1) 1ere question

J'aimerez dans la table emp_audit creer un champ  marequete de type text
et lorsque j'insere un enregistrement exemple insert into values emp ('hubert',12)
dans ma table emp_audit j'ai
operation         I ok
    tampon         heure courante  ok
    id_utilisateur    postgres ok
    nom_employe       hubert ok,
    salaire           12
    marequete ici "insert into values emp ('hubert',12)"  possible ?

Merci et bonne année a tous

    CREATE TABLE emp (
    nom_employe       text NOT NULL,
    salaire           integer
);

       
CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    tampon            timestamp NOT NULL,
    id_utilisateur    text      NOT NULL,
    nom_employe       text      NOT NULL,
    salaire           integer,
   marequete text

);
       
CREATE OR REPLACE FUNCTION audit_employe() 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 trigger AFTER
END;
$emp_audit$ language plpgsql;
       
CREATE TRIGGER emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE audit_employe();

Marc Cousin

Membre

Re : trigger

Il y a au moins cette solution là pour récupérer l'ordre SQL ayant appelé la fonction :

CREATE OR REPLACE FUNCTION fonction_test()
  RETURNS text AS
$BODY$DECLARE
pa text;
BEGIN
SELECT current_query INTO pa from pg_stat_get_activity(pg_backend_pid());
RETURN pa;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Attention, le current_query est de taille limitée (1024 caractères jusqu'à 8.3, spécifié par track_activity_query_size à partir de 8.4)

Dernière modification par Marc Cousin (04/01/2010 12:16:58)

---

Marc.

wilka

Membre

Re : trigger

Bonjour,

Pour cela il faut que vous recreez la requete.

en testant et concatenant les differentes valeurs contenues dans NEW et OLD.

si old.nom_employe est null alors il s'agit d'un INSERT
si new.nom_employee alors c'est un DELETE
pour les autres cas UPDATE

Apres vous reconstituez l'instruction, le probleme c'est que vous ne recuperez pas le where surtout si cela touche plusieurs records avec une seule requete, donc pas tres utilisable.
Le mieux est de stocker les anciennes valeurs (OLD) et les nouvelles (NEW) pour chaque lignes impactées par la requete

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    tampon            timestamp NOT NULL,
    id_utilisateur    text      NOT NULL,
    nom_employe_old       text    ,
    salaire_old           integer,
    nom_employe_new       text    ,
    salaire_new          integer

);

mich30

Membre

Re : trigger

je vais mieux formuler ma demande ce n'est pas clair
est ce possible de concatener tous les paramétres OLD.*,new.*  lors d'un insert ou update ou delete
et de les mettre dans un champ de type text  tous_parametre dans la table emp_audit ?
OLD.* et NEW.* sont des record donc surement je peux patiner ce record pour recuperer
tous les enregistrements :
exemple si je fais insert into emp values ('hubert',12);
dans  tous_parametre =' hubert,12'
Merci

    CREATE TABLE emp (
    nom_employe       text NOT NULL,
    salaire           integer
);

       drop TABLE emp_audit;
       
CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    tampon            timestamp NOT NULL,
    id_utilisateur    text      NOT NULL,
    nom_employe       text      NOT NULL,
    salaire           integer,
    NOM_TABLE          text,
    tous_parametre           TEXT,
);
       
CREATE OR REPLACE FUNCTION audit_employe() 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.*,TG_RELNAME;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*,TG_RELNAME;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*,TG_RELNAME;
        RETURN NEW;
    END IF;
    RETURN NULL; -- le résultat est ignoré car il s'agit d'un trigger AFTER
END;
$emp_audit$ language plpgsql;
       
CREATE TRIGGER emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE audit_employe();

drop  TRIGGER emp_audit on emp;

mich30

Membre

Re : trigger

ok les posts sont arrivés avant mon dernier message j'y reflechis
la methode de marc je vais la tester
et wilka merci j'y reflechis

wilka

Membre

Re : trigger

soit en utilsant la table que j'ai écris precedement

INSERT INTO emp_audit SELECT 'U', now(), user, OLD.nom_employe,OLD.salaire,NEW.nom_employe,NEW.salaire

Apres vous pouvez adapter en concatenant les valeurs (si c'est vraiment votre besoin), mais cela peut etre moins pratique pour les recherche

INSERT INTO emp_audit SELECT 'D', now(), user, OLD.nom_employe||';'||OLD.salaire::text,NEW.nom_employe||';'||NEW.salaire::text

avec cette table
CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    tampon            timestamp NOT NULL,
    id_utilisateur    text      NOT NULL,
    employe_old       text    ,
    employe_new       text 
);

edit: petit oubli ^^
en testant les valeurs null sinon la concatenation avec du null, ce n'est pas terrible

case  when OLD.nom_employe is null then '(null)' else OLD.nom_employe end||';'||case  when OLD.salaire is null then '(null)'::text else OLD.salaire::text end
même chose a faire pour le new

Dernière modification par wilka (04/01/2010 12:40:26)

mich30

Membre

Re : trigger

merci marc encore super
pour le trigger genre drop table create table create role le gere t'il
ou comment faut t'il contourner
merci

gleu

Administrateur

Re : trigger

Il n'existe pas de moyen de récupérer les ordres de modification de structures.

---

Guillaume.

Marc Cousin

Membre

Re : trigger

Pas de triggers sur les ordresDDL, pas de chance
Le mieux c'est d'interdire purement et simplement ces ordres aux utilisateurs.

---

Marc.

mich30

Membre

Re : trigger

c'est ok
lorsque j'insere il y a un enregistrements dans la table audit ok
et lorsque je fait un update il y a 2 enregistrements update dans la table audit
c'est pas FOR EACH ROW du trigger ?
merci

CREATE OR REPLACE FUNCTION FCT_RECUP_REQUETE()
  RETURNS text AS
$BODY$DECLARE
pa text;
BEGIN
SELECT current_query into pa from pg_stat_get_activity(pg_backend_pid());
RETURN pa;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION FCT_RECUP_CLIENT_ADDR()
  RETURNS text AS
$BODY$DECLARE
pa text;
BEGIN
SELECT client_addr from into pa pg_stat_get_activity(pg_backend_pid());
RETURN pa;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
   

/* test
insert into emp values ('mich',12);
update emp set libelle= 15;
select * from emp;

CREATE TABLE emp(
    id                    text,
    libelle          integer
);

CREATE TABLE audit(
    typ_operation         char(1)   NOT NULL,
    Date_Systeme          timestamp NOT NULL,
    id_utilisateur          text      NOT NULL,
    Nom_table              text,
    requete           text,
    client_addr          text
);
       
CREATE OR REPLACE FUNCTION fct_audit() RETURNS TRIGGER AS $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 audit SELECT 'D', now(), user,TG_RELNAME,FCT_RECUP_REQUETE(),FCT_RECUP_CLIENT_ADDR();
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit SELECT 'U', now(), user,TG_RELNAME,FCT_RECUP_REQUETE(),FCT_RECUP_CLIENT_ADDR();
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit SELECT 'I', now(), user,TG_RELNAME,FCT_RECUP_REQUETE(),FCT_RECUP_CLIENT_ADDR();
        RETURN NEW;
    END IF;
    RETURN NULL; -- le résultat est ignoré car il s'agit d'un trigger AFTER
END;
$audit$ language plpgsql;
       
    CREATE TRIGGER trg_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE fct_audit();

gleu

Administrateur

Re : trigger

« FOR EACH ROW » veut dire que le trigger s'exécute pour chaque ligne impactée. Donc si vous faites un UPDATE qui impacte deux lignes, le trigger se déclenchera pour chacune des lignes, donc deux fois.

---

Guillaume.

mich30

Membre

Re : trigger

c'est bizarre je fais un insert dans emp  je fais un update dans emp
dans la table audit j'ai un insert puis 2 lignes updates !!!

gleu

Administrateur

Re : trigger

Une seule raison, il y avait déjà une ligne dans la table.

---

Guillaume.

mich30

Membre

Re : trigger

non non il y avez juste un insert

gleu

Administrateur

Re : trigger

Ce n'est pas possible avec le code que vous nous avez fourni ci-dessus. D'ailleurs, un test sur mon serveur donne ceci :

mich30=# insert into emp values ('mich',12);
INSERT 0 1
mich30=# update emp set libelle= 15;
UPDATE 1
mich30=# select * from emp;
  id  | libelle 
------+---------
 mich |      15
(1 ligne)

mich30=# select * from audit;
 typ_operation |        date_systeme        | id_utilisateur | nom_table |               requete               | client_addr 
---------------+----------------------------+----------------+-----------+-------------------------------------+-------------
 I             | 2010-01-04 15:16:27.387123 | guillaume      | emp       | insert into emp values ('mich',12); | 
 U             | 2010-01-04 15:16:27.393092 | guillaume      | emp       | update emp set libelle= 15;         | 
(2 lignes)

Autrement dit, fonctionnel pour moi.

---

Guillaume.

mich30

Membre

Re : trigger

zut !!! je reteste pas possible !!!

mich30

Membre

Re : trigger

fait encore un insert puis un update tu verras il ya a 2 updates a la suite
merci

gleu

Administrateur

Re : trigger

Même pas besoin de faire le test pour le savoir. Deux lignes dans la table, un UPDATE sur la table entière, donc deux lignes d'UPDATE dans la table d'audit. Rien que du logique.

---

Guillaume.

mich30

Membre

Re : trigger

Guillaume,
ok merci ca m'embete un peu j'aurez aimé qui il y 'ai un update
si tu as 1000 enregistrements , et que je demande un update sur ces lignes
il va me mettre 1000 ligne updates alors que 1 update aurez suffit
FOR EACH STATEMENT  serez pas mieux ?
merci

gleu

Administrateur

Re : trigger

S'il ne faut qu'une insertion dans audit par instruction, il faut « EACH statement ». Mais vous ne pouvez plus regarder le contenu de OLD et de NEW dans ce cas... mais vu la fonction trigger, cela n'a pas d'importance actuellement.

---

Guillaume.

mich30

Membre

Re : trigger

oui ca n'a pas d'importance ,
je viens de faire les tests c'est OK
je te remercie beaucoup
ce forum est super !!
A bientot pour de nouvelles avntures postgres
Mich

mich30

Membre

Re : trigger

pour la 8.4 ca marche
Code:
CREATE OR REPLACE FUNCTION fonction_test()
  RETURNS text AS
$BODY$DECLARE
pa text;
BEGIN
SELECT current_query INTO pa from pg_stat_get_activity(pg_backend_pid());
RETURN pa;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

mais sur la 8.2 cette fonction ne marche pas

ERROR:  function pg_stat_get_activity(integer) does not exist
LINE 1: SELECT current_query  from pg_stat_get_activity(pg_backend_pid(...

j'ai pg_stat_activity mais pas le champ current query ? comme faire merci

mich30

Membre

Re : trigger

je l'ai  sur la 8.2
SELECT current_query  from pg_stat_activity;
mais comment je gere le  (pg_backend_pid());
merci

gleu

Administrateur

Re : trigger

SELECT current_query from pg_stat_activity WHERE procpid=pg_backend_pid();

---

Guillaume.

mich30

Membre

Re : trigger

merci gleu !!