Hi ----- Original Message ----- From: "Frank Fabbrocino" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, June 24, 2004 4:22 AM Subject: [sqlite] Date/Time as Default Value
> I would like to have a default value for a column be the current time. This is basically a poor man's row creation timestamp. In Oracle SQL, I can do the following: > > CREATE TABLE FOO ( > ID NUMBER NOT NULL, > CREATED DATE DEFAULT SYSDATE NOT NULL ); > > So, whenever a row is inserted into the table, you don't need to set the 2nd column because it is automatically set with the date/time of when the insert occured. How would I do the equivalent of this in SQLite? I've tried everything with "date" and "now" that I could think of but no luck... > there are some date functions (see http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions) but I can't get these accepted as default value. However, a trigger can be set up to insert the date when a new record is inserted. Try CREATE TABLE foo ( id NUMBER NOT NULL, created DATE); CREATE TRIGGER insert_date AFTER INSERT ON foo BEGIN UPDATE foo SET created = DATETIME('NOW') WHERE rowid = new.rowid; END; Lawrence --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/04 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]