i'm using SQLite3 and i have a very simple table:
create table pulseCount (
pulses INTEGER NOT NULL,
timestamp DATETIME DEFAULT (1000*(strftime('%s','now')+mod(strftime('%f','now'),1)))
);
problem is, when i update the pulses
value, the timestamp doesn't update as well. i read that i need a trigger to do this, but i wasn't able to make head or tails of the discussion and resolve it to something concrete or useful. and, yes, i do need to be keeping track down to the millisecond.
should be noted that, as far as i can tell, the timestamp updates on INSERT
, but not on UPDATE
, which is what is needed.
anyone willing to lend a hand here?
EDIT:
as requested, the UPDATE
command:
UPDATE pulseCount SET pulses=1;
as there's only one row, there's no need for a WHERE
clause.
EDIT:
well, i managed to bash together a trigger that seems to work just fine:
CREATE TRIGGER [UpdateTimestamp]
AFTER UPDATE ON pulseCount
WHEN old.pulses <> new.pulses
BEGIN
UPDATE pulseCount
SET timestamp = (1000*(strftime('%s','now')+mod(strftime('%f','now'),1)))
WHERE pulses >= 0;
END
if anyone sees a problem with this, please do let me know. i don't know that the final WHERE
clause is needed, but it can't hurt... although if it can save me a few milliseconds, i'd like to know.
UPDATE
command, for anyone to figure out what may be wrong.pulseCount
but not thetimestamp
. You need to update both.