Re: Trigger with dynamic SQL

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(at)gmail(dot)com>

> 2010/5/24 Josi Perez (3T Systems) <josiperez3t(at)gmail(dot)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) <josiperez3t(at)gmail(dot)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
>