On Thu, Aug 09, 2007 at 06:14:43PM +0200, Leif B. Kristensen wrote:
> On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
> >You should probably use a trigger (a before one maybe) instead of a
> > rule.
>
> I tried that too, but I'm still quite shaky on how to write triggers,
> and the same thing happened there: the inserted record was immediately
> deleted. I solved the problem temporarily with two lines in PHP:
You have to use a BEFORE trigger for this to work, unless you're careful
about how you build your where clause. The AFTER trigger is going to see
the row that you just inserted, so you'd have to explicitly exclude it
from the DELETE.
> function set_last_selected_place($place) {
> pg_query("DELETE FROM recent_places WHERE place_fk = $place");
> pg_query("INSERT INTO recent_places (place_fk) VALUES ($place)");
> }
>
> As my application is single-user, and everything is already wrapped up
> in a transaction anyway, there's no real problem with this. But I'd
> still like to understand how to do it 'properly' inside the DB.
Better than what you're doing right now would be to wrap everything into
a function and just call that. Depending on your design, that could be
more (or less) "correct" than trying to do it with a trigger.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)