After Update Triggers

[Bob Pawley <rjpawley@xxxxxxx>]

 

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