При обновлении current_timestamp с помощью SQLite

Я хочу обновить поле с текущей меткой времени, когда строка обновляется.

В MySQL я бы сделал, объявив таблицу

LastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

Но часть "on update" не работает с SQLite. Я не мог найти способ сделать это автоматически, мне нужно объявить триггер?

РЕДАКТИРОВАТЬ. Для записи здесь есть текущий триггер:

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

Спасибо

Ответ 1

Да, вам нужно использовать триггер. (Просто проверьте: правильно ли работает ваш триггер? На первый взгляд это выглядит хорошо для меня.)

MySQL ON UPDATE CURRENT_TIMESTAMP - довольно уникальный, одноцелевой ярлык. Что есть, то есть; эта конструкция не может использоваться аналогично для любых других значений или для любых типов столбцов, кроме TIMESTAMP. (Обратите внимание, как эта функция определена на странице TIMESTAMP вместо страницы CREATE TABLE, поскольку эта функциональность специфична для столбцов TIMESTAMP, а не операторов CREATE TABLE в целом.) Также стоит упомянуть, что, хотя она специфична для типа TIMESTAMP, SQLite даже не имеют разные типы даты и времени.

Насколько я знаю, никакая другая СУБД не предлагает этот ярлык вместо использования фактического триггера. Из того, что я прочитал, триггеры должны использоваться для выполнения этого в MS SQL, SQLite, PostgreSQL и Oracle.

Одна последняя заметка для прохожих:

Это не следует путать с предложениями ON UPDATE в отношении ограничений внешнего ключа. Это нечто совершенно иное, что, вероятно, все RDBMS, которые поддерживают ограничения внешнего ключа, имеют (включая MySQL и SQLite).

Ответ 2

Джон правильно относится к настройкам SQLite по умолчанию, этот триггер приводит к бесконечному циклу. Чтобы избежать рекурсии, используйте предложение WHEN.

Следующее будет работать, даже если параметр recursive_triggers включен:

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;

Ответ 3

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

Ответ 4

Есть более эффективный, красивый и чистый способ сделать это, например:

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

Код, подобный этому, был протестирован в моем проекте. Глубокое объяснение триггера sqlite можно найти здесь https://www.sqlite.org/lang_createtrigger.html