on update current_timestamp with SQLite

Question

I want to update a field with the current timestamp whenever the row is updated.

In MySQL I would do, when declaring the table

LastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

But the "on update" part does not work with SQLite. I could not find a way to do it automatically, do I need to declare a trigger?

EDIT: For the record, here is my current trigger:

CREATE TRIGGER [UpdateLastTime]
AFTER UPDATE
ON Package
FOR EACH ROW
BEGIN
UPDATE Package SET LastUpdate = CURRENT_TIMESTAMP WHERE ActionId = old.ActionId;
END

Thanks

To SQLite experts: don't be shy to post "you must write a trigger" if that's the answer. — Álvaro González, Jul 05 '11 at 06:54
If you're wondering where `ActionId` and `old.ActionId` come from, `ActionId` is a column in Jonas' Package table, and `old` is defined by SQLite as a reference to the updated rows. (See: http://www.sqlite.org/lang_createtrigger.html) — Arel, Jul 09 '14 at 20:36
I don't know if something has changed in the meantime (SQLite 3.31 here), but neither aMarCruz's solution nor Jixter's one seem to work for me. The first one doesn't fire the trigger ; the last one doesn't prevent recursion: it just (hopefully) ends at some point when CURRENT_TIMESTAMP reachs the next second. This may or may not happen before the SQLITE_MAX_TRIGGER_DEPTH is reached, depending on how fast is the update. Trying it on :memory: database, I was able to get a 'too many levels of trigger recursion' message. The only viable solution seems to be the Dmitrij's one. — Giovanni Zezza, Jun 02 '20 at 16:54
I recommend using `UPDATE OF` for all columns but the one holding the date. Ther will be no recursion and the sytnax is simple. See [sqlite-triggers](https://www.sqlite.org/lang_createtrigger.html) — DrMarbuse, Aug 01 '23 at 08:06

score 30 · Accepted Answer · answered Jul 05 '11 at 16:02

Yes, you'd need to use a trigger. (Just checking: is your posted trigger working correctly? At first glance, it looks fine to me.)

MySQL's ON UPDATE CURRENT_TIMESTAMP is a pretty unique, single-purpose shortcut. It is what it is; this construct cannot be used similarly for any other values or for any column types other than TIMESTAMP. (Note how this functionality is defined on the TIMESTAMP type page instead of the CREATE TABLE page, as this functionality is specific to TIMESTAMP columns and not CREATE TABLE statements in general.) It's also worth mentioning that while it's specific to a TIMESTAMP type, SQLite doesn't even have distinct date/time types.

As far as I know, no other RDBMS offers this shortcut in lieu of using an actual trigger. From what I've read, triggers must be used to accomplish this on MS SQL, SQLite, PostgreSQL, and Oracle.

One last note for passersby:

This is not to be confused with ON UPDATE clauses in relation to foreign key constraints. That's something entirely different, which likely all RDBMSs that support foreign key constraints have (including both MySQL and SQLite).

Thank you for the comprehensive answer! (Yes the trigger works fine :)) — Jonas Schmid, Jul 05 '11 at 16:48
To me it looks like this trigger leads to an infinite loop. The trigger itself triggers a new update. And the new update triggers the trigger again. And again and again. — John Smith Optional, Jan 19 '14 at 22:57
@John Smith Optional: Probably not. http://www.sqlite.org/pragma.html#pragma_recursive_triggers — William T. Mallard, Jan 21 '14 at 17:27
@William T.Mallard: Thanks! This was the info I was looking for when I posted this question: http://stackoverflow.com/questions/21223434/do-sqlite-triggers-trigger-other-triggers If you have time to make a reply to my post I'll accept your answer. — John Smith Optional, Jan 21 '14 at 19:42

score 15 · Answer 2 · edited Mar 22 '17 at 13:42

15

John is correct about the default SQLite settings, this trigger leads to an infinite loop. To avoid recursion, use the WHEN clause.

Following will work even if the recursive_triggers setting is on:

PRAGMA recursive_triggers=1;     --- test

CREATE TRIGGER [UpdateLastTime]
    AFTER UPDATE
    ON package
    FOR EACH ROW
    WHEN NEW.LastUpdate < OLD.LastUpdate    --- this avoid infinite loop
BEGIN
    UPDATE Package SET LastUpdate=CURRENT_TIMESTAMP WHERE ActionId=OLD.ActionId;
END;

edited Mar 22 '17 at 13:42

AamirR

11,672
4
59
73

answered Mar 17 '15 at 09:11

aMarCruz

2,434
1
16
14

This question was posted almost 4 years ago. Maybe things have changed. It worked fine back then. – Jonas Schmid Mar 17 '15 at 09:14
4

Should `WHEN NEW.LastUpdate < OLD.LastUpdate` be `WHEN NEW.LastUpdate = OLD.LastUpdate` instead? Since we want the trigger to run when the value hasn't changed. – Danny Guo Jul 06 '20 at 18:40
1

I needed to replace `<` with `=` (otherwise the trigger never runs). – lonix Jan 02 '22 at 05:13
Im pretty sure it intended to be = yes, but this could also theoretically send it into a loop if two updates happen at the same time, though it would exit once the second changes. – Mooing Duck Jun 03 '23 at 14:17
after looking over the Sqlite docs, this answer does indeed have an infant loop if recursion is enabled, even with the `=` fix. – Mooing Duck Jun 03 '23 at 14:36

Fernando Abreu · Answer 3 · 2023-02-18T12:29:00.083

This one is old, but I ran into this when trying to auto update SQLite like all. The proposed solutions helped, but still need a fix. Here it goes:

CREATE TRIGGER [UPDATE_DT]
    AFTER UPDATE ON table_name FOR EACH ROW
    WHEN OLD.field_name = NEW.field_name OR OLD.field_name IS NULL
BEGIN
    UPDATE table_name SET field_name=CURRENT_TIMESTAMP WHERE unique_field=NEW.unique_field;
END;

Explaining:

OLD and NEW are "temporary" instances used by the server, between stored (old) and to-be-updated (new) data
WHEN must compare with = in order to run when updating all data except the updated_field_name value... and must accept manual updates of the field itself without entering an infinite loop
Then WHEN also needs to happen in case the field was never set, so the IS NULL clause, because the = is not enough
Once the trigger is AFTER UPDATE, the WHERE needs to find the updated registry from the NEW unique or primary key, because even keys can be updated ;-)

Dmitrij · Answer 4 · 2018-05-19T22:17:56.710

0

There is more efficient, nice and clean way to do it, for example:

-- List all required fields after 'OF' except the LastUpdate field to prevent infinite loop
CREATE TRIGGER UpdateLastTime UPDATE OF field1, field2, fieldN ON Package
BEGIN
  UPDATE Package SET LastUpdate=CURRENT_TIMESTAMP WHERE ActionId=ActionId;
END;

The code like this one has been tested in my project. Deep sqlite trigger explanation can be found here https://www.sqlite.org/lang_createtrigger.html

edited May 19 '18 at 22:17

answered May 19 '18 at 21:58

Dmitrij

17
2

3

I'm not sure if something has changed, but for me, this solution sets `LastUpdate` on EVERY row when any row is updated. It works as expected if I replace the where clause to `WHERE ActionId=NEW.ActionId`. – andyvanee Oct 14 '20 at 18:07

score -2 · Answer 5 · answered Sep 29 '17 at 15:20

-2

-- Describe UPDATELASTTIME  
CREATE TRIGGER [UpdateLastTime]  
    AFTER   
    UPDATE  
    ON test
    FOR EACH ROW   
    WHEN NEW.last_update_ts <= OLD.last_update_ts  
BEGIN  
    update test set last_update_ts=CURRENT_TIMESTAMP where id=OLD.id;  
END  

-- Describe TEST  
CREATE TABLE "main"."test" (  
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,  
    "name" TEXT,  
    "last_update_ts" DATETIME DEFAULT CURRENT_TIMESTAMP  
);

answered Sep 29 '17 at 15:20

Jixster

7
2

5

I'm not going to downvote you, but I don't think this cuts it here. There is no text explanation, and the answer you gave is for MySQL not SQLite, which is what help is being asked for. (Yes, the original presented a MySQL example, but he/she is looking for Sqlite help) – Cameron Feb 22 '21 at 00:16

on update current_timestamp with SQLite

5 Answers5

Linked

Related