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
