From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | Bob Pawley <rjpawley(at)shaw(dot)ca> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Old/New |
Date: | 2010-01-25 18:38:52 |
Message-ID: | 4B5DE53C.60708@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2010-01-25 18:53:55 | Scale 8X Exhibition Booth Attendants |
Previous Message | Tom Lane | 2010-01-25 18:31:01 | Re: Fwd: Questions about connection clean-up and "invalid page header" |