Re: For each statement trigger and update table
Mike Martin <[email protected]> Sat, 4 Jan 2020 00:32:32 +0000
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAOwYNKbw=xVZyN7LLChynsEJSw=DGxbx4z0MHVZxqA9C=DO6MQ@mail.gmail.com> |
--000000000000647ade059b45905d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Will do tomorrow On Sat, 4 Jan 2020, 00:26 Rene Romero Benavides, <[email protected]> wrote: > Mike, please include to the mailing list as well, so others can help you > out too. Why do you need the trigger to be FOR EACH STATEMENT? so I can > understand your use case, even if it's simple stuff, please share with us > your code. > > On Fri, Jan 3, 2020 at 6:06 PM Rene Romero Benavides < > [email protected]> wrote: > >> Oh, so you're defining transition relations (REFERENCING NEW TABLE, OLD >> TABLE ) as in here? >> >> CREATE TRIGGER paired_items_update >> AFTER UPDATE ON paired_items >> REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab >> FOR EACH ROW >> EXECUTE FUNCTION check_matching_pairs(); >> >> >> On Fri, Jan 3, 2020 at 5:55 PM Mike Martin <[email protected]> wrote: >> >>> According to the docs, not possible to use a transition table and colum= n >>> list together >>> >>> On Fri, 3 Jan 2020, 23:39 Rene Romero Benavides, < >>> [email protected]> wrote: >>> >>>> > I can give code when I get home, but it's pretty simple stuff >>>> please do so, along with your trigger definition. Are you aware that >>>> you can define your update trigger to fire on a specific column? >>>> >>>> https://www.postgresql.org/docs/current/sql-createtrigger.html >>>> >>>> For UPDATE events, it is possible to specify a list of columns using >>>> this syntax: >>>> >>>> UPDATE OF column_name1 [, column_name2 ... ] >>>> >>>> >>>> >>>> On Fri, Jan 3, 2020 at 5:21 PM Mike Martin <[email protected]> >>>> wrote: >>>> >>>>> Not sure if this is possible >>>>> Basically I want to have a trigger which updates an array column in >>>>> the same table when a column is updated >>>>> This works as a row level trigger, but not as per statement >>>>> I have hit the recursive issue (where update fires update trigger >>>>> which fires etc) >>>>> According to the docs I cannot use columns and relative tables togeth= er >>>>> >>>>> So any suggestions? I can give code when I get home, but it's pretty >>>>> simple stuff >>>>> >>>> >>>> >>>> -- >>>> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. >>>> Thomas Alva Edison >>>> http://pglearn.blogspot.mx/ >>>> >>>> >> >> -- >> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. >> Thomas Alva Edison >> http://pglearn.blogspot.mx/ >> >> > > -- > El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. > Thomas Alva Edison > http://pglearn.blogspot.mx/ > > --000000000000647ade059b45905d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"auto">Will do tomorrow=C2=A0</div><br><div class=3D"gmail_quote= "><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 4 Jan 2020, 00:26 Rene Rome= ro Benavides, <<a href=3D"mailto:[email protected]">rene.romero.b@= gmail.com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style= =3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir= =3D"ltr">Mike, please include to the mailing list as well, so others can he= lp you out too. Why do you need the trigger to be FOR EACH STATEMENT? so I = can understand your use case, even if it's simple stuff, please share w= ith us your code.=C2=A0</div><br><div class=3D"gmail_quote"><div dir=3D"ltr= " class=3D"gmail_attr">On Fri, Jan 3, 2020 at 6:06 PM Rene Romero Benavides= <<a href=3D"mailto:[email protected]" target=3D"_blank" rel=3D"no= referrer">[email protected]</a>> wrote:<br></div><blockquote class= =3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg= b(204,204,204);padding-left:1ex"><div dir=3D"ltr">Oh, so you're definin= g transition relations (REFERENCING NEW TABLE, OLD TABLE ) as in here?<div>= <pre style=3D"box-sizing:border-box;font-family:monospace,monospace;font-si= ze:14.4px;overflow:auto;color:rgb(13,10,11);border-radius:0.25rem;border:1p= x solid rgb(206,212,218);margin-top:1rem;margin-bottom:1rem;background-colo= r:rgb(248,249,250);padding:0.8rem">CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE FUNCTION check_matching_pairs();</pre></div></div><br><div clas= s=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020= at 5:55 PM Mike Martin <<a href=3D"mailto:[email protected]" target= =3D"_blank" rel=3D"noreferrer">[email protected]</a>> wrote:<br></div= ><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border= -left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">Accord= ing to the docs, not possible to use a transition table and column list tog= ether=C2=A0</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"g= mail_attr">On Fri, 3 Jan 2020, 23:39 Rene Romero Benavides, <<a href=3D"= mailto:[email protected]" target=3D"_blank" rel=3D"noreferrer">rene.r= [email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote"= style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p= adding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">>=C2=A0 I can give co= de when I get home, but it's pretty simple stuff=C2=A0</div><div>please= do so, along with your trigger definition. Are you aware that you can defi= ne your update trigger to fire on a specific column?</div><div><br></div><d= iv><a href=3D"https://www.postgresql.org/docs/current/sql-createtrigger.htm= l" rel=3D"noreferrer noreferrer" target=3D"_blank">https://www.postgresql.o= rg/docs/current/sql-createtrigger.html</a>=C2=A0=C2=A0<br></div><div><p sty= le=3D"box-sizing:border-box;color:rgb(13,10,11);font-family:"Open Sans= ",sans-serif;font-size:14.4px;margin:1rem 0px 1rem 2rem">For=C2=A0<tt = style=3D"box-sizing:border-box;border-radius:0.25rem;margin:0.6rem 0px;font= -size:0.9rem;color:inherit;background-color:rgb(248,249,250)">UPDATE</tt>= =C2=A0events, it is possible to specify a list of columns using this syntax= :</p><pre style=3D"box-sizing:border-box;font-family:monospace,monospace;fo= nt-size:14.4px;overflow:auto;color:rgb(13,10,11);border-radius:0.25rem;bord= er:1px solid rgb(206,212,218);margin-top:1rem;margin-bottom:1rem;margin-lef= t:2rem;background-color:rgb(248,249,250);padding:0.8rem">UPDATE OF <tt styl= e=3D"box-sizing:border-box;font-weight:900;font-style:italic;border-radius:= 0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:inherit">column_name1</tt>= [, <tt style=3D"box-sizing:border-box;font-weight:900;font-style:italic;bo= rder-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:inherit">colum= n_name2</tt> ... ]</pre></div><div><br></div></div><br><div class=3D"gmail_= quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020 at 5:21 PM= Mike Martin <<a href=3D"mailto:[email protected]" rel=3D"noreferrer = noreferrer" target=3D"_blank">[email protected]</a>> wrote:<br></div>= <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-= left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">Not sur= e if this is possible<div dir=3D"auto">Basically I want to have a trigger w= hich updates an array column in the same table when a column is updated</di= v><div dir=3D"auto">This works as a row level trigger, but not as per state= ment</div><div dir=3D"auto">I have hit the recursive issue (where update fi= res update trigger which fires etc)</div><div dir=3D"auto">According to the= docs I cannot use columns and relative tables together</div><div dir=3D"au= to"><br></div><div dir=3D"auto">So any suggestions? I can give code when I = get home, but it's pretty simple stuff=C2=A0</div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= >El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E= dison<br><a href=3D"http://pglearn.blogspot.mx/" rel=3D"noreferrer noreferr= er" target=3D"_blank">http://pglearn.blogspot.mx/</a><br><div style=3D"padd= ing:0px;margin-left:0px;margin-top:0px;overflow:hidden;color:black;font-siz= e:10px;text-align:left;line-height:130%"></div><div><br></div></div> </blockquote></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= >El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E= dison<br><a href=3D"http://pglearn.blogspot.mx/" target=3D"_blank" rel=3D"n= oreferrer">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:0px;mar= gin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10px;text= -align:left;line-height:130%"></div><div><br></div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= >El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E= dison<br><a href=3D"http://pglearn.blogspot.mx/" target=3D"_blank" rel=3D"n= oreferrer">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:0px;mar= gin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10px;text= -align:left;line-height:130%"></div><div><br></div></div> </blockquote></div> --000000000000647ade059b45905d--