Michael
Perhaps we can look at the following as a simple example of what is
happening-
---------
create or replace function loop_association() returns trigger as $$
begin
Insert Into p_id.loops (monitor)
select new.devices_id
from p_id.devices ;
return null ;
end ;
$$ language plpgsql ;
create trigger loop after insert on p_id.devices
for each row execute procedure loop_association();
------
This trigger and procedure gives a single row on the first insert on an
otherwise blank table. However it produces two identical rows of the second
device_id on the second insert and three identical rows of the third
device_id on the third insert. (This is the only trigger on the table)
If I read your message correctly the trigger is firing on each row of the
originating table and each time it fires it produces a row on the secondary
table for the current NEW.device_id.
How can I correct this action?
Bob
----- Original Message -----
From: "Michael Fuhr" <mike@xxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Postgresql" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Tuesday, August 22, 2006 1:58 PM
Subject: Re: [GENERAL] Inserting Data
On Tue, Aug 22, 2006 at 09:02:52AM -0700, Bob Pawley wrote:
I set aside the procedure you sent to me as it resulted in multiple rows
of
the same information. (In fact one variation produced 100 rows for each
of
the 9 "new" fields creating a 900 row table.
If it was doing that then it would be a good idea to understand
why. If the INSERT ... SELECT matched several rows then several
rows would be inserted, and if the trigger fired for several rows
then several INSERTs would be run.
In contrast here is the trigger for the tables with which I am now
working.
As best as I can determine the two triggers are the same format.
Note the trigger is an 'after update' as opposed to 'after insert'.
[...]
This trigger results in three rows of each "new" field.
What's the exact update command and how many rows in p_id.devices
does it affect? If the update modifies three rows then the trigger
will fire three times (because it's defined FOR EACH ROW), resulting
in three inserts. That could explain the insert-vs-update difference
because an ordinary insert affects only one row. If you add a RAISE
statement to the trigger function then you'll see when and how many
times it's being called.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
