On Sun, 21 Aug 2005, Mark de Vries wrote: > > > I have tried two versions of the trigger: > > > > > > CREATE TRIGGER task_list_1 > > > AFTER INSERT ON task_list > > > BEGIN > > > UPDATE task_list > > > SET insert_ts = datetime('now','localtime'), > > > last_upd_ts = datetime('now','localtime') > > > WHERE task_id = NEW.task_id; > > > END; > > > > > > and, thinking that update might only be allowed to populate one column > > > at a time, > > > > > > CREATE TRIGGER task_list_1 > > > AFTER INSERT ON task_list > > > BEGIN > > > UPDATE task_list SET insert_ts = datetime('now','localtime') > > > WHERE > > > task_id = NEW.task_id; > > > UPDATE task_list SET last_upd_ts = datetime('now','localtime') > > > WHERE > > > task_id = NEW.task_id; > > > END; > > > > > > > > > > As far as I can see, both TRIGGERs above will only happen after INSERT, > > and therefore, will insert exactly the same time in both insert_ts and > > last_upd_ts defeating the intended purpose of the two fields. > > That's one of the problems I ran into... > > > Perhaps you want to create one AFTER INSERT and one AFTER UPDATE > > TRIGGER. > > No, that does not work... Think; the AFTER INSERT trigger does what? > Which triggers? So you still end up with both collumns set after the just > the initial insert.
Hmm... just thinking; are you guarenteed to get the same time in this case? Does CURRENT_TIMESTAMP get the real 'current timestamp', or the time the transaction started? If it is the latter then you could tell it was not modified if insert_ts=last_upd_ts (Provided it's impossible for your app to insert & update in the same second.) If it's always the real current time you couldn't even be sure of that... Rgds, Mark