Search Postgresql Archives

Re: Old/New

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Following is the format with which I have had great success using "New" in After Insert triggers.

Insert into p_id.devices (p_id_id, process_id, fluid_id, status, process_graphics_id, device_description) select (p_id.processes.p_id_id), (p_id.processes.process_id), (p_id.processes.fluid_id), ('Pump #1'), ('11'), ('Pump')
from p_id.processes
where new.pump1 = 'True';

However when the above is used on an After Update trigger on a table with two rows, I get both rows inserted.

(For a while I thought it was a quirk in the actions of the interface, but it happens even when I update pump1 using only PostgreSQL.

This is the reason that I have been avoiding Update triggers until now, so, if anyone can help my understanding of what is happening I would appreciate it.

Bob





----- Original Message ----- From: "Tom Lane" <tgl@xxxxxxxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Postgresql" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, January 21, 2010 3:16 PM
Subject: Re:  Old/New


"Bob Pawley" <rjpawley@xxxxxxx> writes:
I am getting a strange result when using the following -

 Select fluid_id into fluidid
 from p_id.processes
 where new.pump1 = 'True'
 and old.pump1 = 'False'
  or old.pump1 is null;

The fluid_id return is fine when there is a single row. However with two rows, and updating only one of the rows, I quite often get the fluid_id for the other row.

That WHERE condition isn't constraining the SELECT at all; you're
getting the result from the first row in the table.  I think you have
some fundamental confusion about how to work with OLD and NEW in
triggers.  They're just rowtype variables, you do not need to select
from the table to examine them.

regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux