on update current_timestamp with SQLite

user_183665 0
Viewed 39603

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

triggers sqlite
4 Answers

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

user_96983480 
-- 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  
);
user_86964230
Related