Vous n'êtes pas identifié(e).
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();
Hors ligne
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.
Hors ligne
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
);
Hors ligne
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;
Hors ligne
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
Hors ligne
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)
Hors ligne
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
Hors ligne
Il n'existe pas de moyen de récupérer les ordres de modification de structures.
Guillaume.
Hors ligne
Pas de triggers sur les ordresDDL, pas de chance 
Le mieux c'est d'interdire purement et simplement ces ordres aux utilisateurs.
Marc.
Hors ligne
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();
Hors ligne
« 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.
Hors ligne
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 !!!
Hors ligne
Une seule raison, il y avait déjà une ligne dans la table.
Guillaume.
Hors ligne
non non il y avez juste un insert
Hors ligne
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.
Hors ligne
zut !!! je reteste pas possible !!!
Hors ligne
fait encore un insert puis un update tu verras il ya a 2 updates a la suite
merci
Hors ligne
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.
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
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
Hors ligne
je l'ai sur la 8.2
SELECT current_query from pg_stat_activity;
mais comment je gere le (pg_backend_pid());
merci
Hors ligne
SELECT current_query from pg_stat_activity WHERE procpid=pg_backend_pid();Guillaume.
Hors ligne
merci gleu !!
Hors ligne