Re: Inserting Data

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(at)fuhr(dot)org>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Postgresql" <pgsql-general(at)postgresql(dot)org>
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