Re: Trigger with dynamic SQL

[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