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

Answer 1

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).

Answer 2

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;

Answer 3

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 ;-)

Answer 4

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

Answer 5

-- 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  
);