Re: After Insert Trigger

On Mon, 9 Feb 1998, Mark Collins wrote: > > > > 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. > > > > Correct but the requested example above was indeed for an INSERT TRIGGER. > > Yes, the example was for an INSERT TRIGGER, but Jon also mentioned that the database had a > column called "lastupdated" and that they envisioned using "an 'after Insert/Update' > Trigger". I assumed that they would also try creating an UPDATE TRIGGER to perform a > similar function. True but in an update trigger it is permitted to update the current table as long as the column(s) being updated in the trigger is (are) not mentioned in the SET clause of the triggering statement so if the original UPDATE does not include lastupdated and bywhom, and it is apparent that Jon does not want the application code doing so, it is perfectly valid to UPDATE these columns in the UPDATE trigger. This can be done in a FOR EACH ROW clause. Therefore INSERT statements are the only real problem and a DEFAULT constraint is the correct solution to the problem. I assumed that by mentioning ONLY the INSERT TRIGGER that Jon understood that the UPDATE TRIGGER was not a problem. Art S. Kagel, kagel@bloomberg.com