Triggers - Updating the Table that caused the Trigger

> Hi > > > I'm trying to perform a calculation on a table after a user using our > manufacturing system has updated it. > > I've tried creating a trigger that execute a Stored Procedure (SP) and > then updates the table, > but this causes error -747. > > I then tried creating a table to hold the triggers, creating two SP's, > the first inserting a row into the triggering table when op_mtm_code is > changed, > and the second updating op_proctime when a row is inserted into the > triggering table. > This also fails. > > If I disable the second trigger, and then run the SP manually it works > fine. > Is there any work around, for updating a table that caused the trigger, > either by some delaying routine or system call ? > > Thanks in advance. > > Wayne Sheldon > Computer Manager > Airflow Streamlines plc > Production Division > > > > > ERROR -747 (Table or column matches object referenced in triggering > statement.) > > This error is returned when a triggered SQL statement acts on the > triggering table, or when both statements are updates, and the column that > is updated in the triggered action is the same as the column that the > triggering statement updates.) > > > > > Details > -------- > > INFORMIX-OnLine Version 7.20.UC5 > > Table OPS ( > op_item char, > op_opno char, > op_mtm_code char, > op_mtm_freq float, > op_mtm_interval float, > op_mtm_proctime float. > ) > > Table MTM ( > mtm_code char, > mtm_time float > ) > > The calculation is simple: > > Read MTM using mtm_code to obtain mtm_time > > Update OP_mtm_proctime = ((mtm_time * op_mtm_freq) / > op_mtm_interval) > >