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? > > You can instead alter to table to include DEFAULT clauses for each of > these columns: > > alter table tablename modify (lastupdated datetime year to fraction(2)> default CURRENT), modify (bywhom char(8) default USER); > > That will take care of your requirement. Unless things have changed, the DEFAULT clause will only affect the column value at the time of insert, assuming that no value is specified on the VALUES clause of the INSERT statement. It will not alter an existing value during an UPDATE. Mark Collins mcollins@us.dhl.com Windows 95: 32 bit extensions and a graphical shell for a 16 bit patch to an 8 bit operating system originally coded for a 4 bit processor, written by a 2 bit company that can't stand 1 bit of competition.