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