Informix Update Triggers

Hello, I have a need to execute a stored procedure conditionally based on a specific column and it's new value. The current debate is the use of a WHEN clause in a FOR EACH ROW, as opposed to UPDATE FOR column_name in the trigger declaration. [INFORMIX environment] My position is that the UPDATE FOR will execute when the specified column changes without regard for the value in the column. The column value then must be passed to and tested in the stored procedure. The alternative approach would be to use the FOR EACH ROW WHEN(new.columnX <> old.columnX and new.columnX = desired_value. While either approach will work I like getting rid of decisions as soon as possible rather than deferring them downstream. I am thinking that the UPDATE FOR column_name is generating the same background code as the WHEN clause so it really has no advantages in my case. Does an update trigger execute whenever a table is updated and then decide if something needs to be done? Would an UPDATE FOR column_name be more efficient than the FOR EACH ROW WHEN method, assuming the value of the new data is important? Thanks, Jim

On 19 Dec 2006 12:27:55 -0800, James F Smith wrote: > I have a need to execute a stored procedure conditionally based on a > specific column and it's new value. The current debate is the use of a > WHEN clause in a FOR EACH ROW, as opposed to UPDATE FOR column_name in > the trigger declaration. [INFORMIX environment] > > My position is that the UPDATE FOR will execute when the specified > column changes without regard for the value in the column. The column > value then must be passed to and tested in the stored procedure. The > alternative approach would be to use the FOR EACH ROW WHEN(new.columnX > <> old.columnX and new.columnX = desired_value. You can argue various ways...the one I think I'd use is: If you specify the WHEN clause, the server will evaluate the WHEN condition and, when it is satisfied, execute the trigger body (and the trigger body might need to re-evaluate the condition). If you do not specify the WHEN clause, the trigger body will be executed each time, and will need to evaluate the condition. So, using a WHEN clause means that the condition will be evaluated each time, and sometimes the trigger body will be executed and may re-evaluate the condition; not using a WHEN clause means the trigger body will always be executed and the condition executed just once. Which is more efficient may well depend on the rate at which the trigger needs to be fired; is the WHEN clause usually satisfied or usually not satisfied - and does the triggered action re-evaluate the condition. > While either approach will work I like getting rid of decisions as soon > as possible rather than deferring them downstream. I am thinking that > the UPDATE FOR column_name is generating the same background code as > the WHEN clause so it really has no advantages in my case. > > Does an update trigger execute whenever a table is updated and then > decide if something needs to be done? Would an UPDATE FOR column_name > be more efficient than the FOR EACH ROW WHEN method, assuming the value > of the new data is important? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/