On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote:
> Hi there,
>
> I'm trying this for quite some time... and I'm totally stuck.
>
> I have the following table:
>
> CREATE TABLE example(
> date integer primary key not null,
> text text,
> ctime TIMESTAMP,
> mtime TIMESTAMP);
>
> ctime=creation time (should be set only once)
> mtime=modification time (should be set every time the row is updated)
>
change ctime definition to
ctime DEFAULT CURRENT_TIMESTAMP
and use trigger for only the mtime
> Important: I also want to be able to "insert or replace" rows and keep ctime.
>
> Currently I'm trying with 2 triggers:
>
> CREATE TRIGGER insert_example_timestamp AFTER INSERT ON example
> BEGIN
> UPDATE example SET ctime = DATETIME('now','localtime')
> WHERE ( rowid = new.rowid AND ctime IS NULL);
> END;
>
> CREATE TRIGGER update_example_timestamp AFTER UPDATE ON example
> BEGIN
> UPDATE example SET mtime=DATETIME('now','localtime') WHERE rowid = new.rowid;
> END;
>
> The update trigger works fine, but the insert trigger ALWAYS updates ctime.
> I tried various combinations to query a unset/empty ctime and only
> update if ctime is NULL or ''.
> It doesn't work inside the trigger.
> Either it updates always or never.
> Where is my mistake?
>
> Is there another way to achieve this? Maybe without triggers?
>
> Many thanks!
> _______________________________________________
> 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
