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@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, January 21, 2010 3:16 PM
Subject: Re: [GENERAL] Old/New
> "Bob Pawley" <rjpawley@shaw.ca> 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
