That's interesting. I didn't get into an infinite loop, but the column wasn't updated either.<br><br>After changing it to BEFORE instead of AFTER, it worked, including on insert. And I didn't need to change opaque to trigger. Thanks for everyone's help.
<br><br><div><span class="gmail_quote">On 8/14/07, <b class="gmail_sendername">Kevin C. Smith</b> <<a href="mailto:kevin@linuxsmith.com">kevin@linuxsmith.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On Tue, 2007-08-14 at 19:51 -0500, Kevin C. Smith wrote:<br>> On Tue, 2007-08-14 at 18:22 -0500, Tim Wilson wrote:<br>> ><br>> ><br>> > On 8/14/07, Kevin C. Smith <<a href="mailto:kevin@linuxsmith.com">
kevin@linuxsmith.com</a>> wrote:<br>> > ><br>> > > CREATE FUNCTION set_last_update() RETURNS opaque AS '<br>> > > BEGIN<br>> > > NEW.last_updated:=now
();<br>> > > RETURN NEW;<br>> > > END;<br>> > > ' LANGUAGE 'plpgsql';<br>> > ><br>> > > CREATE TRIGGER set_last_update AFTER INSERT OR UPDATE ON
<br>> > people FOR EACH<br>> > > ROW<br>> > > EXECUTE PROCEDURE set_last_update();<br>> > ><br>> ><br>> > Okay I see maybe two issues (disclaimer I don't know what the
<br>> > hell I'm<br>> > talking<br>> > about).<br>> ><br>> > Wouldn't a trigger AFTER insert or update become in infinite<br>> > loop, and maybe
<br>> > postgresql therefore ignores it? Just a guess.<br>> ><br>> > I thought trigger updates were different, but I guess it's possible.<br>> ><br>> ><br>> > I also think that were you have 'opaque' you need 'trigger'.
<br>> ><br>> ><br>> > The 'trigger' type caused the parser to complain. However, I'll try<br>> > it again when I can. It's possible the other things I did caused<br>> > 'trigger' to be valid. But my guess is 'opaque' works in
7.1, but<br>> > 'trigger' was added later.<br>> ><br>> ><br>> > I tested this and it worked:<br>> ><br>> ><br>> > CREATE OR REPLACE FUNCTION set_last_update()
<br>> > RETURNS "trigger" AS<br>> > BEGIN<br>> > NEW.last_updated = now();<br>> > RETURN NEW;<br>> > END;<br>> > LANGUAGE 'plpgsql' VOLATILE;
<br>> ><br>> ><br>> > CREATE TRIGGER set_last_update<br>> > BEFORE INSERT OR UPDATE<br>> > ON test_table<br>> > FOR EACH ROW<br>> > EXECUTE PROCEDURE set_last_update();
<br>> ><br>> ><br>> > I chose AFTER because I didn't want the timestamp to be affected if<br>> > for some reason the update failed. I also thought if it happened<br>> > before the insert, what would it be updating, since the record didn't
<br>> > exist yet.<br>><br>> I wondered about INSERT also, but seems to work fine. Give it a try.<br>> Not sure how it's actually being handled, but it works.<br>><br>> AFTER update looks like a loop to me, since the trigger fires
<br>> on insert or update of a row and itself updates a row in the same<br>> table it watches for updates.<br>><br>> Yeah, I forgot to mention that I'm using version 8.2.4, so it may<br>> be slightly different for your situation.
<br>><br>> Thanks for the exercise!<br>> I'd been meaning to try out triggers in Postgresql,<br>> after my trying some in MySQL.<br><br>P.S.<br>Searched for "postgresql after update trigger loop" on google and found
<br>this:<br><br><a href="http://blog.revsys.com/2006/08/automatically_u.html">http://blog.revsys.com/2006/08/automatically_u.html</a><br><br>Wish he would have reference something authoritative on that the<br>loop question.
<br><br><br><br>--<br>Religion is regarded by the common people as true, by the wise as false,<br>and by the rulers as useful. --- Lucius Annaeus Seneca<br><br>_______________________________________________<br>Cialug mailing list
<br><a href="mailto:Cialug@cialug.org">Cialug@cialug.org</a><br><a href="http://cialug.org/mailman/listinfo/cialug">http://cialug.org/mailman/listinfo/cialug</a><br></blockquote></div><br><br clear="all"><br>-- <br>Tim