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" <t...@sss.pgh.pa.us>
To: "Bob Pawley" <rjpaw...@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, January 21, 2010 3:16 PM
Subject: Re: [GENERAL] Old/New
"Bob Pawley" <rjpaw...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
