Re: Old/New

On 01/25/2010 10:24 AM, Bob Pawley wrote:
> The suggestions received have worked well for one update in the row.
>
> However, if I make any other update on the same row the trigger fires
> and more inserts are generated.
>
> However. I have found that the 8.5 alpha version has this addition -
>
> http://developer.postgresql.org/pgdocs/postgres/release-8-5.html
>
> "Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression to
> be checked to determine whether the trigger should be fired."
>
> Would this change solve my problem, or is there another solution around?
>
> Bob

From your previous thread on this problem:
http://archives.postgresql.org/pgsql-general/2010-01/msg00777.php

I suggested this:

Create an INSERT, UPDATE trigger on table1. Have the trigger inspect the
value of pump1. You will need to guard against double entry on updates.
So rough flow
is:

if TG_OP = 'INSERT' and NEW.pump1 = 't'
INSERT row second table
if TG_OP = 'UPDATE' and NEW.pump1='t'
if OLD.pump1 = 'f' or OLD.pump1 is NULL
INSERT row second table

You need to verify whether the pump1='t' is actually a change or just
carry over from the previous version of the row. As it stands now your
trigger functions sees NEW.pump1='t' and issues an INSERT regardless of
the previous state of pump1.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com