Using DEFAULT values for columns

I am trying to define a column on a table that will contain the last time the row was touched: create table ULCustomer ( cust_id integer not null, cust_name varchar(30), last_modified datetime year to fraction(3), primary key ( cust_id ) ); I am able to create the table this way, but I was trying to use the default CURRENT so I don't have to specify the column in the insert and let the database set it for me. I can't seem to get the syntax right drop table ULCustomer; create table ULCustomer ( cust_id integer not null, cust_name varchar(30), last_modified datetime default current, primary key ( cust_id ) ); I tried the above, but it fails with a syntax error. If someone could give me a quick example, it would be appreciated. Next question, extends this one. I assume a default value of CURRENT will only assign the date on an INSERT. Is there anyway to define a column/default so that the datetime field is updated everytime the row is updated as well. I would prefer to do this without writing a trigger. I will also be using the serial default, so if someone has used it an example would be useful. create table ULCustomer ( cust_id integer default serial, cust_name varchar(30), last_modified datetime year to fraction(3), primary key ( cust_id ) ); Thanx in advance.

David Fishburn wrote: > I am trying to define a column on a table that will contain the last time > the row was touched: > > create table ULCustomer ( > cust_id integer not null, > cust_name varchar(30), > last_modified datetime year to fraction(3), > primary key ( cust_id ) > );> > I am able to create the table this way, but I was trying to use the default > CURRENT so I don't have to specify the column in the insert and let the > database set it for me. > > I can't seem to get the syntax right > drop table ULCustomer; > create table ULCustomer ( > cust_id integer not null, > cust_name varchar(30), > last_modified datetime default current, > primary key ( cust_id ) > ); last_modified datetime year to second default current year to second not null, > > I tried the above, but it fails with a syntax error. > > If someone could give me a quick example, it would be appreciated. > > Next question, extends this one. > I assume a default value of CURRENT will only assign the date on an INSERT. Correct, and only if last_modified is not mentioned in the INSERT list (implicitly or explicitly). > Is there anyway to define a column/default so that the datetime field is > updated everytime the row is updated as well. I would prefer to do this > without writing a trigger. Prefer away, but the trigger is necessary. When I do this, I create a view on the table which omits the last_modified column (and last_username column). I then do my inserts into the view, which allows me to update all the columns in the view. The trigger on the table arranges to assign the last_modified and last_username columns when a row is inserted or updated. > I will also be using the serial default, so if someone has used it an > example would be useful. > create table ULCustomer ( > cust_id integer default serial, cust_id serial default 0 not null primary key, > > cust_name varchar(30), > last_modified datetime year to fraction(3), > primary key ( cust_id ) > ); -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>