Re: Trigger with dynamic SQL

["Josi Perez (3T Systems)" <josiperez3t@xxxxxxxxx>]

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 <mabewlun@xxxxxxxxx>

2010/5/24 Josi Perez (3T Systems) <josiperez3t@xxxxxxxxx>

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) <josiperez3t@xxxxxxxxx>

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