Alex Hunsaker, thank you for your suggestion, but, the processor do not replace OLD.TG_ARGV[1] by the content. If I use OLD in EXECUTE it is not recognized. I didn't get how to pass an integer variable to the trigger.
I already create repeteadly times the trigger changing the UPDATE comand inside the TG_OPER DELETE for each table, but, I really appreciate to know if there is a solution for this. Thank you in advance, Josi Perez 2010/5/24 Josi Perez (3T Systems) <josipere...@gmail.com> > 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 >> > >