[GENERAL] 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 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
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
Re: [GENERAL] After Update Triggers
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 [EMAIL PROTECTED] 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
Re: [GENERAL] After Update Triggers
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] After Update Triggers
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
