After Update Triggers - Mailing list pgsql-general

From Bob Pawley
Subject After Update Triggers
Date
Msg-id 004e01c70a97$7ec57e00$8e904618@owner
Whole thread Raw
Responses Re: After Update Triggers
List pgsql-general
 
Hi All
 
I have three tables
 
Create Table Pipe
( pipe_id serial ,
fluid_id int4
) ;
 
Create Table Equipment
  (Equipment_id serial,
    fluid_id int4
) ;
 
Create Table Processes
   ( Fluid_id serial
    fluid varchar (15),
    ip_op_equipment varchar (5)
    ) ;
 
The interface inserts the name of the fluid into column processes.fluid.
 
This is immediately followed by an update to column processes.ip_op_equipment of either "ip', 'op' or 'eq'.
 
Using the following trigger the fluid_id of the Process table is to be distributed to either the pipe or equipment under the following circumstance.
 
1 - If ip_op_equipment = 'ip' or 'op' insert the fluid_id into the Pipes table.
2 - If ip_op_equipment - 'eq' insert into Equipment.
-------
 Create or Replace function base() returns trigger as $$
 begin
 
 if new.ip_op_equipment = 'ip' or new.ip_op_equipment = 'op'
 or new.ip_op_equipment = 'oth'
 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 aa1 after update on p_id.processes
 for each row execute procedure base();
-------
When I trigger 'after insert' the function doesn't work because the ip_op_equipment condition is an update. When I manually enter directley into the table this trigger works fine when both the fluid and ip_op_equipment are entered as one entry.
 
When I trigger 'after update' every row in the Processes table is inserted into the other tables depending on the conditionals. I end up with multiple inserts of the same information.
 
Is it possible to create a trigger that inserts only one row for each entry?
 
Bob Pawley
 
 

pgsql-general by date:

Previous
From: "Matt Miller"
Date:
Subject: Allowing SYSDATE to Work
Next
From: Alvaro Herrera
Date:
Subject: Re: Allowing SYSDATE to Work