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.klaver@gmail.com
