Thank you. The trigger: CREATE OR REPLACE FUNCTION logdata() RETURNS trigger AS $BODY$DECLARE arg_table varchar; arg_id varchar; arg_old integer; qry text;
BEGIN arg_table := TG_ARGV[0]; arg_id := TG_ARGV[1]; --field to use OLD.id arg_old := TG_ARGV[2]; --value if TG_OP = 'INSERT' then new.userinc := current_user; new.dtinc := 'now'; return new; elseif TG_OP = 'UPDATE' then new.useralt := current_user; new.dtalt := 'now'; return new; *elseif TG_OP = 'DELETE' then ---just user postgresW can delete if current_user <> 'postgresW' then -- trying to mount the SQL --qry := 'UPDATE '||arg_table||' set userexc = ' ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' || arg_id ||' = OLD.'||TG_ARGV[1]; qry := 'UPDATE '||arg_table||' set userexc = ' ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' || arg_id ||' = '||to_char(arg_old,'999999'); --qry := 'update '||quote_ident(arg_table)||"set dtexc = now, userexc = current_user "||"where "||quote_ident(arg_id)||"=OLD."||quote_ident(arg_id)||";"; raise notice 'QRY = %', qry; EXECUTE qry; --EXECUTE 'UPDATE '||arg_table||' set userexc = ' ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' || arg_id ||' = OLD.'||TG_ARGV[1]; --EXECUTE 'update '||quote_ident(arg_table)||' set userexc ='|| current_user ||' where '||quote_ident(arg_id)||' = OLD.'||quote_ident(arg_id)||';'; --update opcao set dtexc = 'now', userexc = current_user -- where idopcao = OLD.idopcao; return NULL;* else return OLD; end if; end if; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION logdata() OWNER TO postgres; To each table: CREATE TRIGGER logdatatable BEFORE INSERT OR UPDATE OR DELETE ON opcao FOR EACH ROW EXECUTE PROCEDURE logdata('op', 'idop', idop); I could not send the current idop (integer) to mount a SQL without OLD. The goal is to use the same trigger changing just the parameters in each table. Thanks in advance, Josi Perez 2010/5/24 Szymon Guz <mabew...@gmail.com> > 2010/5/24 Josi Perez (3T Systems) <josipere...@gmail.com> > > Sorry for the inconvenience, but no one have ideas to solve this problem? >> Am I in the wrong list to ask this? >> Need I create triggers for each table? >> >> Thanks in advance for any suggestions. >> Josi Perez >> >> 2010/5/19 Josi Perez (3T Systems) <josipere...@gmail.com> >> >> To avoid to delete registers I created one trigger activated "before >>> delete" with lines like that: >>> UPDATE tableX set dtExc = 'now', userExc = current_user where idTableX = >>> OLD.idTableX; >>> return NULL; >>> >>> but, I need do the same for many tables and I don't catch how. >>> I created an sql variable to construct the update command using >>> parameters on trigger >>> qry := 'UPDATE '||arg_table||' set userexc = ' >>> ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' || >>> arg_id ||' = OLD.'||TG_ARGV[1]; >>> >>> but when "EXECUTE qry" I lost the OLD.variable. >>> >>> I can't send the bigint id to delete in trigger parameters. >>> >>> Any suggestions? >>> >>> Thanks in advance, >>> Josi Perez >>> >>> >> > What is the problem? What do you mean by "lost the OLD.variable"? Better > show us the whole trigger code as I really don't get it. > > regards > Szymon Guz >