Re: Inserting Data

Hi Michael

Yes - Multiple rows of the same data are created in each secondary table.

I have two triggers that are identical in format although handling different
tables. One is triggeres after insert and with this there is no multiplying
factor.

The other is triggered after an update.

Both triggers use NEW.* in the same manner. However, the trigger after
update gives multiple results of the same information.

Is there any way around this problem? Is there perhaps a method restricting
the trigger to an update to a particular column rather than the table as a
whole?

Bob



----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Friday, August 18, 2006 7:27 PM
Subject: Re: [GENERAL] Inserting Data


> On Fri, Aug 18, 2006 at 09:27:19AM -0700, Bob Pawley wrote:
>> I need to insert data from one table into three other tables.
>>
>> I attempted the following format.
> [...]
>>  insert into p_id.loops (monitor)
>>  Select p_id.devices.devices_id
>>  Where p_id.devices.device_number = library.devices.device_number
>>  and library.devices.type_ = 'mon' ;
>
> Style recommendation: add a FROM clause to these queries.  Missing
> FROM clauses are nonstandard and can cause unexpected results.
> PostgreSQL 8.0 and earlier allow such queries by default but in 8.1
> they're disabled by default.  See the add_missing_from configuration
> setting:
>
> http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#GUC-ADD-MISSING-FROM
>
>> Unfortunately this gave multiple results on the target tables.
>
> What do you mean by "multiple results"?  Do you mean that each row
> inserted into p_id.devices causes multiple rows to be inserted into
> p_id.loops, p_id.settings, and p_id.alarms?  The trigger function's
> query doesn't reference the new row that was inserted into p_id.devices;
> it joins the entire table against library.devices.  Did you mean to
> do something like the following?
>
> INSERT INTO p_id.loops (monitor)
> SELECT NEW.devices_id
> FROM library.devices
> WHERE NEW.device_number = library.devices.device_number
> AND library.devices.type_ = 'mon';
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings