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@xxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Postgresql" <pgsql-general@xxxxxxxxxxxxxx>
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
