Re: Using trigger to set timestamp

For inserts, define a DEFAULT for 'update_time (or 'insert_time'), and don't load on insert. The system will do this for you. For updates, as far as I know, you will have to define the columns (and remember to change the trigger as you add/delete columns). You cannot reference the column to be updated by the trigger in the trigger itself. e.g. CREATE TABLE timestamp_test ( the_primary_key serial NOT NULL, description varchar(30), inserted_dttm datetime YEAR to SECOND DEFAULT CURRENT YEAR to SECOND, updated_dttm datetime YEAR to SECOND ); insert into timestamp_test (the_primary_key, description) values (0, "Just a test");--------- The above will insert a row, the time appears in inserted_dttm create trigger t_timestamp_test UPDATE OF the_primary_key, description on timestamp_test REFERENCING OLD AS pre NEW AS post FOR EACH ROW ( UPDATE timestamp_test SET updated_dttm = CURRENT YEAR to SECOND where pre.the_primary_key = the_primary_key );--- Notice you only reference the'data' columns, not the timestamp columns === Schuyler Southwell Maricopa County Attorney's Offfice Phoenix, AZ 85003 >>> Henk van der Geld <H.van.der.Geld@net.HCC.nl> 09/25/99 01:49PM >>> Hello, I'm not to familiar with triggers and stored procedures. What I want to do is to stick a timestamp in every record in the database. My idea was to use two triggers for every table; one for the insert action and one for the update action. Every record has a column eg 'update_time'. I want to define the triggers in such a way that they remain working even if the table structure changes. So I don't want to mention the column names in the trigger. But this seems to be impossible. If I do an update of the table, as a consequence the update trigger is activated and should update the 'update_time' field. This will cause another update, so the update trigger is activated again. Informix prohibits this situation to happen. So my question: is what I should do to make this all happen? Again, I don't want to mention all the column names in the trigger, because that creates a dependancy that I don't want. Thanks, Henk vd Geld