Informix Update Triggers
Posted in 2006
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