Hi Michael
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.
I went back to an earlier procedure which has been performing successfully.
------
create or replace function base() returns trigger as $$
begin
insert into p_id.specifications (fluid_id) values (new.fluid_id);
if new.ip_op_equipment = 'ip'or new.ip_op_equipment = 'op'
then
insert into p_id.pipes (fluid_id) values (new.fluid_id);
elseif
new.ip_op_equipment = 'eq'
then
insert into p_id.equipment (fluid_id) values (new.fluid_id);
end if;
return null;
end;
$$ language plpgsql ;
create trigger fluid after insert on p_id.processes
for each row execute procedure base();
------------
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'.
CREATE OR REPLACE FUNCTION p_id.valves_mon()
RETURNS "trigger" AS
$$
begin
if new.type_ = 'end'
then
insert into p_id.association (valve) values (new.devices_id) ;
elseif
new.type_ = 'mon'
then
insert into p_id.loops (monitor) values (new.devices_id) ;
end if ;
return null;
end ;
$$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER loop
AFTER UPDATE
ON p_id.devices
FOR EACH ROW
EXECUTE PROCEDURE p_id.valves_mon();
This trigger results in three rows of each "new" field.
I must admit I am having a little trouble fully understanding the basic
PostgreSQL structure.
What seems to me to be a logical procedure almost always has problems that I
need to sort through.
Help is greatly appreciated.
Bob.
----- Original Message -----
From: "Michael Fuhr" <mike@xxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Postgresql" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, August 21, 2006 4:47 PM
Subject: Re: [GENERAL] Inserting Data
On Mon, Aug 21, 2006 at 08:27:58AM -0700, Bob Pawley wrote:
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.
The insert-vs-update distinction might be a red herring; the
difference in behavior might be a result of the queries run inside
the trigger functions. Or maybe the statements executed by the
update trigger are firing additional triggers. Without more
information we can only guess.
Both triggers use NEW.* in the same manner. However, the trigger after
update gives multiple results of the same information.
How are the triggers using NEW? In your original message the
function didn't use NEW at all.
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?
Do you mean "particular row" instead of "particular column"?
If you're executing INSERT ... SELECT statements from inside a
trigger function as in your original message, then the restriction
on the SELECT determines how many rows are inserted. It's possible
that those inserts are causing additional triggers to fire. Have
you added any RAISE statements to the trigger functions to see when
they're being called?
Could you post a simple, self-contained example that exhibits both
the desired and undesired behavior? That is, all SQL statements
that somebody could load into an empty database to create and
populate the tables, create the triggers, and perform whatever
actions are necessary to elicit both behaviors.
--
Michael Fuhr
