Thread: After Update Triggers
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
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 ;
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();
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
> 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? Hello, I've read the whole message several times and I have to admit I still don't understand what are you trying to do or what is going wrong. I'm not sure what do you mean by 'when I trigger after insert' - the trigger is defined as AFTER UPDATE so naturally it does not fire in case of an INSERT. Anyway the point is you can define the trigger as AFTER INSERT OR UPDATE and use TG_OP variable, or maybe define several triggers - one for the UPDATE, one for the INSERT. Tomas
I am attempting to distribute the fluid from the process table to its own table (pipe or equipment) depending on whether the fluid is classified as op, ip or eq. I didn't include the after insert trigger as there can't be a trigger until the ip_op_equipment is updated. BTW what is TG_OP that you referred to? Bob ----- Original Message ----- From: "Tomas Vondra" <tv@fuzzy.cz> To: <pgsql-general@postgresql.org> Sent: Friday, November 17, 2006 3:34 PM Subject: Re: [GENERAL] After Update Triggers >> 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? > > Hello, > > I've read the whole message several times and I have to admit I still > don't understand what are you trying to do or what is going wrong. > > I'm not sure what do you mean by 'when I trigger after insert' - the > trigger is defined as AFTER UPDATE so naturally it does not fire in case > of an INSERT. > > Anyway the point is you can define the trigger as AFTER INSERT OR UPDATE > and use TG_OP variable, or maybe define several triggers - one for the > UPDATE, one for the INSERT. > > Tomas > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Have you explored the possibility that the trigger is doing what it is supposed to. I would investigate the procedure that updates the ip_op_equipment field. Make sure that it is not updating all the rows each time and thereby firing your trigger for each update. TG_OP is a variable available to trigger functions. It identifies what operation is being done to the row i.e. INSERT,UPDATE,DELETE. It is referenced in the pl/pgsql section of the manual. On Friday 17 November 2006 03:49 pm, Bob Pawley wrote: > I am attempting to distribute the fluid from the process table to its own > table (pipe or equipment) depending on whether the fluid is classified as > op, ip or eq. > > I didn't include the after insert trigger as there can't be a trigger until > the ip_op_equipment is updated. > > BTW what is TG_OP that you referred to? > > Bob > > ----- Original Message ----- > From: "Tomas Vondra" <tv@fuzzy.cz> > To: <pgsql-general@postgresql.org> > Sent: Friday, November 17, 2006 3:34 PM > Subject: Re: [GENERAL] After Update Triggers > > >> 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? > > > > Hello, > > > > I've read the whole message several times and I have to admit I still > > don't understand what are you trying to do or what is going wrong. > > > > I'm not sure what do you mean by 'when I trigger after insert' - the > > trigger is defined as AFTER UPDATE so naturally it does not fire in case > > of an INSERT. > > > > Anyway the point is you can define the trigger as AFTER INSERT OR UPDATE > > and use TG_OP variable, or maybe define several triggers - one for the > > UPDATE, one for the INSERT. > > > > Tomas > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Adrian Klaver aklaver@comcast.net
> I am attempting to distribute the fluid from the process table to its
> own table (pipe or equipment) depending on whether the fluid is
> classified as op, ip or eq.
OK, now I understand.
> I didn't include the after insert trigger as there can't be a trigger
> until the ip_op_equipment is updated.
Please post both triggers and prefferably a small testcase - for example
seveal SQL commands (INSERTs / UPDATEs) demonstrating a failure.
I don't understand what do you mean by "there can't be a trigger until
the ip_op_equipment is updated". Well, by the time the AFTER UPDATE
trigger is fired, the update is already done (that's the AFTER keyword),
but I don't understand on what table is the trigger defined etc.
> BTW what is TG_OP that you referred to?
That's one of the variables defined by PL/pgSQL in each trigger. For
example this one means 'TRIGGERING OPERATION' - a trigger can be defined
for several operations simultaneously (AFTER INSERT OR UPDATE OR
DELETE), and in the body you can do something like
IF TG_OP = 'INSERT' THEN
...
ELSIF TG_OP = 'UPDATE' THEN
...
ELSE
...
END IF;
There are several other useful variables - see the this
http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html
Tomas