使用 SQLite 更新 current_timestamp [英] on update current_timestamp with SQLite

问题描述

我想在更新行时使用当前时间戳更新字段.

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

在 MySQL 中，当声明表时我会这样做

In MySQL I would do, when declaring the table

LastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

但是更新时"部分不适用于 SQLite.我找不到自动执行的方法，是否需要声明触发器?

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

谢谢

推荐答案

是的，您需要使用触发器.(只是检查:您发布的触发器是否正常工作?乍一看，我觉得还不错.)

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 的 ON UPDATE CURRENT_TIMESTAMP 是一个非常独特的单一用途的快捷方式.就是这样;此构造不能类似地用于任何其他值或除TIMESTAMP 之外的任何列类型.(注意如何在 TIMESTAMP 类型页面 而不是 CREATE TABLE 页面，如此功能特定于 TIMESTAMP 列，而不是一般的 CREATE TABLE 语句.)还值得一提的是，虽然它特定于 TIMESTAMP 类型，SQLite 甚至没有明确的日期/时间类型.

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.

据我所知，没有其他 RDBMS 提供这种快捷方式来代替使用实际触发器.据我所知，必须使用触发器在 MS SQL、SQLite、PostgreSQL 和 Oracle 上完成此操作.

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.

给路人的最后一句话:

不要与与外键约束相关的 ON UPDATE 子句混淆.那是完全不同的东西，可能所有支持外键约束的 RDBMS(包括 MySQL 和 SQLite)都有.

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

这篇关于使用 SQLite 更新 current_timestamp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！