on update current_timestamp with SQLite
sqlite, triggers
Solution
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).
Problem
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