Re: After Insert Trigger

Jon Brown wrote: > > Can anyone offer advice? > > We are developing a large client server system with an Informix back end. > Each of the tables in the database has a lastupdated and bywhom field which > we had invisioned setting by use of an 'after Insert/Update' Trigger. > (i.e.) > CREATE TRIGGER post_insert> INSERT ON tablename > REFERENCING NEW AS NEW_INS > FOR EACH ROW ( NEW_INS.lastupdated = CURRENT; > NEW_INS.bywhom = USER); > > It would appear that it is impossible to update an informix table with a > trigger that is on the same table > Is this the case or are we just being stupid? > > Thanks > > Jon It seems like alot of people are battling with this problem. The solution that JL presented is very elegant and simple where you insert into views that don't include the time and user columns. More difficult is updating rows and automatically updating the time and user columns (I noticed the name of your time column). Setting up a trigger, like the one above, doesn't work because the row is already updated before the trigger is activated. Following is a solution which I've been thinking about but haven't gotten around to trying. I'd appreciate any comments about it. 1. Put the time and user columns as well as any other columns that are to be automatically generated in a separate table referencing the main table. 2. Create update and insert triggers on the main table that to automatically update or insert into the related time table. The update trigger can also check if there really was any change to the data and decide not to update the time and user values. I won't get into the actual coding details but I think it's fairly straightforward. For my purposes things get a little bit more complicated because I want to keep a record of all changes in a separate table. To illustrate: main_table history_table ---------- ------------- the_key (PK) <--+<--------------- the_key (PK) some_data | rec_time (PK) | rec_user | some_data time_table | ---------- | the_key (PK) ---+ rec_time rec_user When a record is inserted into main_table, an insert trigger simply inserts the current time and user into time_table. When a main_table row is updated, an update trigger inserts a row into history_table from the old data in main_table and time_table as well as updating time_table. The update trigger wouldn't do anything unless actual changes were made to main_table. Does this make sense? ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl john@rl.is http://www.rl.is/~john/pow4gl.html