Just in case you also run into this: I had to do this recently on a
project of mine and found out that datetime('now') (on Windows)
doesn't give me the actual time since it uses UTC. To get the actual
local time, use datetime('now','localtime').For the rest of the date/time documentation, you can refer to http://sqlite.org/lang_datefunc.html Hope it helps, // -------------------------------------- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com On Fri, Oct 17, 2008 at 9:21 PM, P Kishor <[EMAIL PROTECTED]> wrote: > On 10/17/08, Kristofer Hindersson <[EMAIL PROTECTED]> wrote: >> Hi, >> >> I've recently started to play around with SQLite and I'm currently trying >> to create a trigger for updating a timestamp field inside a table >> (SQL-syntax for the creation of the table is included below.) Could someone >> please tell me how I should go about writing such a trigger. I'd like the >> [updated]-field to be set to the current timestamp everytime an entry is >> updated/modified. >> >> Thanks! >> >> /Kris >> >> CREATE TABLE IF NOT EXISTS Entries ( >> entryID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, >> title VARCHAR(50) NOT NULL, >> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, >> updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, >> signature VARCHAR(25) NOT NULL, >> body TEXT NOT NULL); >> > > > CREATE TRIGGER add_date > AFTER INSERT ON Entries > BEGIN > UPDATE Entries SET updated = datetime('now') WHERE entryID = new. > entryID; > END; > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
