Re: For each statement trigger and update table
Rene Romero Benavides <[email protected]> Fri, 3 Jan 2020 18:26:34 -0600
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CANaGW08w6yEZk-BiEmfG7O6dvV3gt_hmrwnB56oxyy7a0agSyg@mail.gmail.com> |
--000000000000380bb2059b457ba4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 column >> list together >> >> On Fri, 3 Jan 2020, 23:39 Rene Romero Benavides, <[email protected]= m> >> 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 yo= u >>> 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 th= e >>>> 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 whic= h >>>> fires etc) >>>> According to the docs I cannot use columns and relative tables togethe= r >>>> >>>> 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/ > > --=20 El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. Thomas Alva Edison http://pglearn.blogspot.mx/ --000000000000380bb2059b457ba4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Mike, please include to the mailing list as well, so other= s can help you out too. Why do you need the trigger to be FOR EACH STATEMEN= T? so I can understand your use case, even if it's simple stuff, please= share with us your code.=C2=A0</div><br><div class=3D"gmail_quote"><div di= r=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020 at 6:06 PM Rene Romero B= enavides <<a href=3D"mailto:[email protected]">rene.romero.b@gmail= .com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar= gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1= ex"><div dir=3D"ltr">Oh, so you're defining transition relations (REFER= ENCING NEW TABLE, OLD TABLE ) as in here?<div><pre style=3D"box-sizing:bord= er-box;font-family:monospace,monospace;font-size:14.4px;overflow:auto;color= :rgb(13,10,11);border-radius:0.25rem;border:1px solid rgb(206,212,218);marg= in-top:1rem;margin-bottom:1rem;background-color: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">[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"auto">According to the docs, n= ot possible to use a transition table and column list together=C2=A0</div><= br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri,= 3 Jan 2020, 23:39 Rene Romero Benavides, <<a href=3D"mailto:rene.romero= [email protected]" target=3D"_blank">[email protected]</a>> wrote:<br><= /div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo= rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><di= v dir=3D"ltr">>=C2=A0 I can give code when I get home, but it's pret= ty simple stuff=C2=A0</div><div>please do so, along with your trigger defin= ition. Are you aware that you can define your update trigger to fire on a s= pecific column?</div><div><br></div><div><a href=3D"https://www.postgresql.= org/docs/current/sql-createtrigger.html" rel=3D"noreferrer" target=3D"_blan= k">https://www.postgresql.org/docs/current/sql-createtrigger.html</a>=C2=A0= =C2=A0<br></div><div><p style=3D"box-sizing:border-box;color:rgb(13,10,11);= font-family:"Open Sans",sans-serif;font-size:14.4px;margin:1rem 0= px 1rem 2rem">For=C2=A0<tt style=3D"box-sizing:border-box;border-radius:0.2= 5rem;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-fami= ly:monospace,monospace;font-size:14.4px;overflow:auto;color:rgb(13,10,11);b= order-radius:0.25rem;border:1px solid rgb(206,212,218);margin-top:1rem;marg= in-bottom:1rem;margin-left:2rem;background-color:rgb(248,249,250);padding:0= .8rem">UPDATE OF <tt style=3D"box-sizing:border-box;font-weight:900;font-st= yle:italic;border-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:i= nherit">column_name1</tt> [, <tt style=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_name2</tt> ... ]</pre></div><div><br></div></div= ><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fr= i, Jan 3, 2020 at 5:21 PM Mike Martin <<a href=3D"mailto:[email protected]= s.com" rel=3D"noreferrer" target=3D"_blank">[email protected]</a>> wr= ote:<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 sure if this is possible<div dir=3D"auto">Basically I want to ha= ve a trigger which updates an array column in the same table when a column = is updated</div><div dir=3D"auto">This works as a row level trigger, but no= t as per statement</div><div dir=3D"auto">I have hit the recursive issue (w= here update fires update trigger which fires etc)</div><div dir=3D"auto">Ac= cording to the docs I cannot use columns and relative tables together</div>= <div dir=3D"auto"><br></div><div dir=3D"auto">So any suggestions? I can giv= e 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" target= =3D"_blank">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:0px;ma= rgin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10px;tex= t-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">http://p= glearn.blogspot.mx/</a><br><div style=3D"padding:0px;margin-left:0px;margin= -top:0px;overflow:hidden;color:black;font-size:10px;text-align:left;line-he= ight:130%"></div><div><br></div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= class=3D"gmail_signature">El genio es 1% inspiraci=C3=B3n y 99% transpirac= i=C3=B3n.<br>Thomas Alva Edison<br><a href=3D"http://pglearn.blogspot.mx/" = target=3D"_blank">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:= 0px;margin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10= px;text-align:left;line-height:130%"></div><div><br></div></div> --000000000000380bb2059b457ba4--