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)
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
