[sqlite] Set update time, but not always
On 2015/12/13 1:31 PM, Cecil Westerhof wrote:
> I have a table where I would most of the time update a field lastChecked to
> current_date when I update the record. But it is possible that I sometimes
> want to update a record without updating lastChecked. Is this possible, or
> should I update it (almost) always manually?
Not sure what you mean by this... You can either have a field update
automatically, or manually. There is no "sometimes" update automatically.
If you can define another field or table or some way of specifying
whether the date updating should happen, you could use an ON-UPDATE
trigger to update the row's lastChecked when some other queryable value
is TRUE - but in my experience it is much easier in this case to simply
have your program code decide and then add the date update bit to the
update query when needed.
Also - SQLite doesn't have a MySQL-esque "on_update_current_datetime"
specification for a column - it has to be a trigger, though I have used
DEFAULT values that set current date/time with success - like this:
CREATE TABLE t (
a INT,
b NUMERIC DEFAULT (datetime('now','localtime'))
);
INSERT INTO t (a) VALUES (1), (2);
SELECT * FROM t;
a | b
---|-
1 | 2015-12-13 14:17:36
2 | 2015-12-13 14:17:36
[sqlite] Set update time, but not always
2015-12-13 13:18 GMT+01:00 R Smith :
>
> On 2015/12/13 1:31 PM, Cecil Westerhof wrote:
>
>> I have a table where I would most of the time update a field lastChecked
>> to
>> current_date when I update the record. But it is possible that I sometimes
>> want to update a record without updating lastChecked. Is this possible, or
>> should I update it (almost) always manually?
>>
>
> Not sure what you mean by this... You can either have a field update
> automatically, or manually. There is no "sometimes" update automatically.
>
?That was what I thought, but it never hurts to verify.?
> If you can define another field or table or some way of specifying whether
> the date updating should happen, you could use an ON-UPDATE trigger to
> update the row's lastChecked when some other queryable value is TRUE - but
> in my experience it is much easier in this case to simply have your program
> code decide and then add the date update bit to the update query when
> needed.
>
?That was what I was thinking: in all the statements where it is required I
add:
lastChecked = CURRENT_DATE?
?and the one situation it is not needed I do not add it.
Also - SQLite doesn't have a MySQL-esque "on_update_current_datetime"
> specification for a column - it has to be a trigger, though I have used
> DEFAULT values that set current date/time with success - like this:
>
> CREATE TABLE t (
> a INT,
> b NUMERIC DEFAULT (datetime('now','localtime'))
> );
>
?I use something like that already, but sligth?ly different, because I only
need the date:
entered TEXTDEFAULT CURRENT_DATE
--
Cecil Westerhof
[sqlite] Set update time, but not always
I have a table where I would most of the time update a field lastChecked to current_date when I update the record. But it is possible that I sometimes want to update a record without updating lastChecked. Is this possible, or should I update it (almost) always manually? -- Cecil Westerhof
