--0016367b6c7274bb1304876fae43
Content-Type: text/plain; charset=ISO-8859-1
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) <josiperez3t [at] 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 <mabewlun [at] gmail.com>
>
> 2010/5/24 Josi Perez (3T Systems) <josiperez3t [at] 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) <josiperez3t [at] 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
>>
>
>
--0016367b6c7274bb1304876fae43
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Alex Hunsaker, thank you for your suggestion, but, the processor do not rep=
lace OLD.TG_ARGV[1] by the content.<br>If I use OLD in EXECUTE it is not re=
cognized.<br>I didn't get how to pass an integer variable to the trigge=
r.<br>
<br>I already create repeteadly times the trigger changing the UPDATE coman=
d inside the TG_OPER DELETE for each table, but, I really appreciate to kno=
w if there is a solution for this.<br><br>Thank you in advance,<br>Josi Per=
ez<br>
<br><br><div class=3D"gmail_quote">2010/5/24 Josi Perez (3T Systems) <span =
dir=3D"ltr"><<a href=3D"mailto:josiperez3t [at] gmail.com">josiperez3t [at] gmail.=
com</a>></span><br><blockquote class=3D"gmail_quote" style=3D"margin: 0p=
t 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1=
ex;">
Thank you.<br><br>The trigger:<br>CREATE OR REPLACE FUNCTION logdata()<br>=
=A0 RETURNS trigger AS<br>$BODY$DECLARE<br>=A0=A0 arg_table=A0 varchar;<br>=
=A0=A0 arg_id=A0=A0=A0=A0 varchar;<br>=A0=A0 arg_old=A0=A0=A0 integer;<br>=
=A0=A0 qry=A0=A0=A0=A0=A0=A0=A0 text;<br><br>BEGIN<br>
arg_table :=3D TG_ARGV[0];=A0 <br>arg_id=A0=A0=A0 :=3D TG_ARGV[1];=A0=A0=A0=
--field to use OLD.id<br>arg_old=A0=A0 :=3D TG_ARGV[2];=A0=A0 --value<br><=
br>if TG_OP =3D 'INSERT' then<br>=A0=A0 new.userinc :=3D current_us=
er;<br>=A0=A0 new.dtinc :=3D 'now';<br>
=A0=A0 return new;<br>elseif TG_OP =3D 'UPDATE' then<br>=A0=A0 new.=
useralt :=3D current_user;<br>=A0=A0 new.dtalt :=3D 'now';<br>=A0=
=A0 return new;<br><br><b>elseif TG_OP =3D 'DELETE' then<br>=A0=A0 =
---just user postgresW can delete<br>
=A0=A0 if current_user <> 'postgresW' then<br>=A0=A0=A0=A0=A0=
-- trying to mount the SQL<br>=A0=A0=A0=A0=A0 --qry :=3D 'UPDATE '=
||arg_table||' set userexc =3D ' ||chr(39)||current_user||chr(39)||=
', dtalt =3D '||'''now'''||' where '=
; || arg_id ||' =3D OLD.'||TG_ARGV[1];<br>
=A0=A0=A0=A0=A0 qry :=3D 'UPDATE '||arg_table||' set userexc =
=3D ' ||chr(39)||current_user||chr(39)||', dtalt =3D '||'&#=
39;'now'''||' where ' || arg_id ||' =3D '||=
to_char(arg_old,'999999');<br>
<br>=A0=A0=A0=A0=A0 --qry :=3D 'update '||quote_ident(arg_table)||&=
quot;set dtexc =3D now, userexc =3D current_user "||"where "=
||quote_ident(arg_id)||"=3DOLD."||quote_ident(arg_id)||";&qu=
ot;;<br><br>=A0=A0=A0=A0=A0 raise notice 'QRY =3D %', qry;<br>
=A0=A0=A0=A0=A0 EXECUTE qry;<br>=A0=A0=A0=A0=A0 --EXECUTE 'UPDATE '=
||arg_table||' set userexc =3D ' ||chr(39)||current_user||chr(39)||=
', dtalt =3D '||'''now'''||' where '=
; || arg_id ||' =3D OLD.'||TG_ARGV[1];<br>
=A0=A0=A0=A0=A0 --EXECUTE 'update '||quote_ident(arg_table)||' =
set userexc =3D'|| current_user ||' where '||quote_ident(arg_id=
)||' =3D OLD.'||quote_ident(arg_id)||';';<br>=A0=A0=A0=A0=
=A0 --update opcao set dtexc =3D 'now', userexc =3D current_user<br=
>
=A0=A0=A0=A0=A0 -- where idopcao =3D OLD.idopcao;<br>=A0=A0=A0=A0=A0 return=
NULL;</b><br>=A0=A0 else<br>=A0=A0=A0=A0=A0 return OLD;<br>=A0=A0 end if;<=
br>end if;<br>END;$BODY$<br>=A0 LANGUAGE 'plpgsql' VOLATILE<br>=A0 =
COST 100;<br>ALTER FUNCTION logdata() OWNER TO postgres;<br>
<br><br>To each table:<br>CREATE TRIGGER logdatatable<br>=A0 BEFORE INSERT =
OR UPDATE OR DELETE<br>=A0 ON opcao<br>=A0 FOR EACH ROW<br>=A0 EXECUTE PROC=
EDURE logdata('op', 'idop', idop);<br><br>I could not send =
the current idop (integer) to mount a SQL without OLD.<br>
The goal is to use the same trigger changing just the parameters in each ta=
ble.<br><br>Thanks in advance,<br>Josi Perez<br><br><br><br><div class=3D"g=
mail_quote">2010/5/24 Szymon Guz <span dir=3D"ltr"><<a href=3D"mailto:ma=
bewlun [at] gmail.com" target=3D"_blank">mabewlun [at] gmail.com</a>></span><div>
<div></div><div class=3D"h5"><br>
<blockquote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; borde=
r-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class=3D"gma=
il_quote">2010/5/24 Josi Perez (3T Systems) <span dir=3D"ltr"><<a href=
=3D"mailto:josiperez3t [at] gmail.com" target=3D"_blank">josiperez3t [at] gmail.com</=
a>></span><div>
<div></div><div><br><blockquote class=3D"gmail_quote" style=3D"margin: 0pt =
0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex=
;">
Sorry for the inconvenience, but no one have ideas to solve this problem? A=
m I in the wrong list to ask this?<br>Need I create triggers for each table=
?<br><br>Thanks in advance for any suggestions.<br>Josi Perez<br><br><div c=
lass=3D"gmail_quote">
2010/5/19 Josi Perez (3T Systems) <span dir=3D"ltr"><<a href=3D"mailto:j=
osiperez3t [at] gmail.com" target=3D"_blank">josiperez3t [at] gmail.com</a>></span=
><div><div></div><div><br><blockquote class=3D"gmail_quote" style=3D"margin=
: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-lef=
t: 1ex;">
<span style=3D"font-family: verdana,sans-serif;">To avoid to delete registe=
rs I created one trigger activated "before delete" with lines lik=
e that:</span><br style=3D"font-family: verdana,sans-serif;"><span style=3D=
"font-family: verdana,sans-serif;">UPDATE tableX=A0 set dtExc =3D 'now&=
#39;, userExc =3D current_user where idTableX =3D OLD.idTableX;</span><br s=
tyle=3D"font-family: verdana,sans-serif;">
<span style=3D"font-family: verdana,sans-serif;">return NULL;</span><br sty=
le=3D"font-family: verdana,sans-serif;"><br style=3D"font-family: verdana,s=
ans-serif;"><span style=3D"font-family: verdana,sans-serif;">but, I need do=
the same for many tables and I don't catch how.</span><br style=3D"fon=
t-family: verdana,sans-serif;">
<span style=3D"font-family: verdana,sans-serif;">I created an sql variable =
to construct the update command using parameters on trigger</span><br style=
=3D"font-family: verdana,sans-serif;"><span style=3D"font-family: verdana,s=
ans-serif;">=A0=A0=A0=A0=A0 qry :=3D 'UPDATE '||arg_table||' se=
t userexc =3D ' ||chr(39)||current_user||chr(39)||', dtalt =3D '=
;||'''now'''||' where ' || arg_id ||' =
=3D OLD.'||TG_ARGV[1];</span><br style=3D"font-family: verdana,sans-ser=
if;">
<br style=3D"font-family: verdana,sans-serif;"><span style=3D"font-family: =
verdana,sans-serif;">but when "EXECUTE qry" I lost the OLD.variab=
le.</span><br style=3D"font-family: verdana,sans-serif;"><br style=3D"font-=
family: verdana,sans-serif;">
<span style=3D"font-family: verdana,sans-serif;">I can't send the bigin=
t id to delete in trigger parameters.</span><br style=3D"font-family: verda=
na,sans-serif;"><br style=3D"font-family: verdana,sans-serif;"><span style=
=3D"font-family: verdana,sans-serif;">Any suggestions?</span><br style=3D"f=
ont-family: verdana,sans-serif;">
<br style=3D"font-family: verdana,sans-serif;"><span style=3D"font-family: =
verdana,sans-serif;">Thanks in advance,</span><br style=3D"font-family: ver=
dana,sans-serif;"><font color=3D"#888888"><span style=3D"font-family: verda=
na,sans-serif;">Josi Perez</span><br style=3D"font-family: verdana,sans-ser=
if;">
<br><div style=3D"display: inline;"></div>
</font></blockquote></div></div></div><br><div style=3D"display: inline;"><=
/div>
</blockquote></div></div></div><br><div>What is the problem? What do you me=
an by "lost the OLD.variable"? Better show us the whole trigger c=
ode as I really don't get it.</div><div><br></div><div>regards</div>
<div>Szymon Guz</div>
</blockquote></div></div></div><br><div style=3D"display: inline;"></div>
</blockquote></div><br><div style=3D"visibility: hidden; display: inline;" =
id=3D"avg_ls_inline_popup"></div><style type=3D"text/css">#avg_ls_inline_po=
pup { position:absolute; z-index:9999; padding: 0px 0px; margin-left: 0=
px; margin-top: 0px; width: 240px; overflow: hidden; word-wrap: break-w=
ord; color: black; font-size: 10px; text-align: left; line-height: 13px=
;}</style>
--0016367b6c7274bb1304876fae43--
