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