On Sun, 21 Aug 2005, Puneet Kishor wrote:
>
> On Aug 21, 2005, at 9:20 AM, <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > There was some recent discussion about timestamp triggers, something I
> > have been trying to get working on my own. Specifically, I have two
> > fields, insert_ts and last_upd_ts, which I would like to be the insert
> > time and last update time, respectively. However, when I try to write
> > a trigger to populate these fields, only last_upd_ts gets populated.
>
> can someone explain to me why folks try to accomplish the above using
> TRIGGERs when the insert time can be set so easily by simply defining
> the column as
>
> insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP
>
> and last_upd_ts (or its equivalent) can set in the application logic
> and updated during an UPDATE.
Because you depend on the application or person to do 'the right thing',
and/or because I don't want to want to write all those update statements
in all those apps & scripts that access the DB... MUCH easier to just do
it once in the DB....
> That said...
>
> >
> > 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.
You can get a little facy with triggers with WHERE clauses to (try to)
fix that, but then complexity quickly rises to the point where putting it
into the application level is easier.
And if that's not an option because you can't, or don't want, to trust the
applications/scripts/users then sqlite prolly isn't the right tool for the
job....
This is the exact same question I had. And I realized that this is just a
limitation of sqlite. No problem, I will just have to do things a little
different than I'm used to. I my case there is no need to be absolutely
'secure' about the value in the these fields. And the pros of using sqlite
for the project I'm working on outweigh these cons.
Thnx to all who responded to my version of this question.
Regards,
Mark
