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.
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.
Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
TRIGGER.
Although why, I still don't understand.
--
Puneet Kishor
