Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Keith Medcalf]

>So is "julianday('now')" non-deterministic while "julianday()" _is_
>deterministic? That seems a little weird considering they're the same
>thing... right?

Yes.  Same as "datetime(julianday(), '+1 day')" and datetime(datetime(), '+1 
day') are deterministic but "datetime('now', '+1 day')" is not even though they 
all have the same result.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Raymond]

> Alternatively, you can use the new GENERATED ALWAYS AS (...) STORED to make 
> it an automatically updated stored field and you do not need triggers at all, 
> just a version of SQLite3 that does generated columns (version 3.31.0 from 
> 2020-01-22 or later).
> 
> create table MyData
> (
>   id   integer primary key,
>   data,
>   lastupdate real as (julianday()) stored
> );

I thought that generated columns could only use deterministic functions?
https://www.sqlite.org/gencol.html
"2.3. Limitations
...
3. The expression of a generated column may only reference constant literals 
and columns within the same row, and may only use scalar deterministic 
functions. The expression may not use subqueries, aggregate functions, window 
functions, or table-valued functions.
..."


https://www.sqlite.org/deterministic.html
"3. Special-case Processing For Date/Time Functions

The built-in date and time functions of SQLite are a special case. These 
functions are usually considered deterministic. However, if these functions use 
the string "now" as the date, or if they use the localtime modifier or the utc 
modifier, then they are considered non-deterministic. Because the function 
inputs are not necessarily known until run-time, the date/time functions will 
throw an exception if they encounter any of the non-deterministic features in a 
context where only deterministic functions are allowed."


So is "julianday('now')" non-deterministic while "julianday()" _is_ 
deterministic? That seems a little weird considering they're the same thing... 
right?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Keith Medcalf]

On Thursday, 12 March, 2020 09:37, David Blake  wrote:

>What stops the
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
>from also triggering the AFTER UPDATE ON recursively?

>Perhaps a pragma or inSQLite are  triggers non-recursive by default?

>I am using (now I have by semi-colons right)
>CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
>WHEN NEW.LastUpdate <= OLD. LastUpdate
>BEGIN
>UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
>END

>My intention is for the when to avoid infinite calls, but maybe I am
>fooling myself.

If you do not want the trigger to fire recursively you have to write it in such 
a way that it will not fire recursively since anyone can turn recursion on or 
off at any time.  Just because you decided to write a trigger that requires 
that recursive_triggers be turned off does not mean that recursive_triggers are 
turned off, merely that your design is insufficient.

It also depends if you want the "lastupdate" field to be an auditable field 
(that is, it is only changed when a row is updated and cannot otherwise be 
changed) or not.  If you want to make it an auditable field that cannot be 
tampered with, then you need many triggers to make that work properly.  

Alternatively, you can use the new GENERATED ALWAYS AS (...) STORED to make it 
an automatically updated stored field and you do not need triggers at all, just 
a version of SQLite3 that does generated columns (version 3.31.0 from 
2020-01-22 or later).

create table MyData
(
  id   integer primary key,
  data,
  lastupdate real as (julianday()) stored
);

(of course, you can put other "stuff" such as storing a iso8601 text timestamp 
if you want to (a) use more space and (b) have less precision)
(if you want a unixepoch secondstamp the use ((julianday()-2440587.5)*86400.0). 
 Resolution is only to the millisecond as that is all that is maintained 
internally and even the julianday double precision floating point format has 
enough significant digits to accurately portray milliseconds until well after 
we have to solve the Year 10K problem.)

Generated columns makes auditable "lastupdate" type data as simple to implement 
as using triggers to implement "createdon" type auditable data fields.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Simon Slavin]

On 12 Mar 2020, at 3:36pm, David Blake  wrote:

> What stops the
> UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
> from also triggering the AFTER UPDATE ON recursively?
> 
> Perhaps a pragma or inSQLite are  triggers non-recursive by default?

Bingo.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Raymond]

> What stops the
> UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
> from also triggering the AFTER UPDATE ON recursively?
> 
> Perhaps a pragma or inSQLite are  triggers non-recursive by default?
> 
> I am using (now I have by semi-colons right)
> CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
> WHEN NEW.LastUpdate <= OLD. LastUpdate
> BEGIN
> UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
> END
> 
> My intention is for the when to avoid infinite calls, but maybe I am
> fooling myself.

Recursive triggers are off by default.
Otherwise you could always add checks into the WHEN clause for seeing if any of 
the other fields was actually updated.

WHEN NEW.LastUpdate <= OLD.LastUpdate
 AND
 (   --Something actually changed
 NEW.Field1 is not OLD.Field1
 OR
 NEW.Field2 is not OLD.Field2
 OR ...
 --Think you want to exclude LastUpdate from this OR'd list of changed 
fields to check
 )

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Ling, Andy]

>Thanks Andy

>> In addition, the role of the "when" clause is unclear.  Is it necessary?
>>
>> I don't think it is. I have a very similar trigger which I've been 
>> using for several years And it doesn't have the where...
>>
>> CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports  FOR EACH ROW  
>> BEGIN
>>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;  
>> END
>>

>What stops the
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ; from also 
>triggering the AFTER UPDATE ON recursively?

>Perhaps a pragma or inSQLite are  triggers non-recursive by default?

It looks like this answers your question...

https://stackoverflow.com/questions/21223434/do-sqlite-triggers-trigger-other-triggers

Andy Ling



**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Blake]

Thanks Andy

> In addition, the role of the "when" clause is unclear.  Is it necessary?
>
> I don't think it is. I have a very similar trigger which I've been using
> for several years
> And it doesn't have the where...
>
> CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports
>  FOR EACH ROW
>  BEGIN
>UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
>  END
>

What stops the
UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
from also triggering the AFTER UPDATE ON recursively?

Perhaps a pragma or inSQLite are  triggers non-recursive by default?

I am using (now I have by semi-colons right)
CREATE TRIGGER tgrUpdate AFTER UPDATE ON myTable FOR EACH ROW
WHEN NEW.LastUpdate <= OLD. LastUpdate
BEGIN
UPDATE myTable SET  LastUpdate = DATETIME('now') WHERE id = OLD.id;
END

My intention is for the when to avoid infinite calls, but maybe I am
fooling myself.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Ling, Andy]

>On 12/03/2020 08:47, David Blake wrote:
>> I'm looking for an easy way to maintain a last updated column for each 
>> record in several tables and considering if using a triggers is viable.
>>
>> I thought that defining a trigger like this on each table would work
>>
>> CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
>>FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>>BEGIN
>>UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
>>END

...

> In addition, the role of the "when" clause is unclear.  Is it necessary?

I don't think it is. I have a very similar trigger which I've been using for 
several years
And it doesn't have the where...

CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports
 FOR EACH ROW
 BEGIN
   UPDATE ports SET timeofday = CURRENT_TIMESTAMP WHERE id = NEW.id ;
 END

Andy


**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank You.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Jean-Luc Hainaut]

On 12/03/2020 08:47, David Blake wrote:

I'm looking for an easy way to maintain a last updated column for each
record in several tables and considering if using a triggers is viable.

I thought that defining a trigger like this on each table would work

CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
   BEGIN
   UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
   END


As far as I know, updating the current row in a "before" trigger is a 
"nop" in SQLite since this row does not exist yet (for my information, 
could you check?). However, this works fine in an "after" trigger.


In addition, the role of the "when" clause is unclear.  Is it necessary?

JLH


--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel 
antivirus Avast.
https://www.avast.com/antivirus

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Blake]

Ha, my error - missing ";" in the UPDATE clause but I was blind.

On Thu, 12 Mar 2020 at 08:18, David Blake  wrote:

> Thanks for such a swift reply, good to know that it should work (without
> typos)
>
> >Thank you very much for keeping the error message secret.
>
> near "END": syntax error:
>
>
> I'm testing out ideas using DB Browser, but will try in my app and see if
> this is just a DB Browser issue
>
> On Thu, 12 Mar 2020 at 08:04, Clemens Ladisch  wrote:
>
>> David Blake wrote:
>> > CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
>> >   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>> >   BEGIN
>> >   UPDATE my_tableSET  lastupdated = DATETIME('now') WHERE id =
>> NEW.id
>> >   END
>> >
>> > The WHEN clause is an attempt to avoid infinite recursion that UPDATE
>> > within an UPDATE would cause.
>>
>> Typical UPDATE statements will leave this field with its old value, so
>> it might be a better ideas to use <= instead of <.
>>
>> > However I get SQL errors when I try defining a trigger this way this
>> > in my favorite db dbrowser.
>>
>> Thank you very much for keeping the error message secret.
>>
>> When I fix the wrong table table name and add the missing semicolon after
>> the UPDATE statement, this trigger works fine.
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[David Blake]

Thanks for such a swift reply, good to know that it should work (without
typos)

>Thank you very much for keeping the error message secret.

near "END": syntax error:


I'm testing out ideas using DB Browser, but will try in my app and see if
this is just a DB Browser issue

On Thu, 12 Mar 2020 at 08:04, Clemens Ladisch  wrote:

> David Blake wrote:
> > CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
> >   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
> >   BEGIN
> >   UPDATE my_tableSET  lastupdated = DATETIME('now') WHERE id = NEW.id
> >   END
> >
> > The WHEN clause is an attempt to avoid infinite recursion that UPDATE
> > within an UPDATE would cause.
>
> Typical UPDATE statements will leave this field with its old value, so
> it might be a better ideas to use <= instead of <.
>
> > However I get SQL errors when I try defining a trigger this way this
> > in my favorite db dbrowser.
>
> Thank you very much for keeping the error message secret.
>
> When I fix the wrong table table name and add the missing semicolon after
> the UPDATE statement, this trigger works fine.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

[Clemens Ladisch]

David Blake wrote:
> CREATE TRIGGER my_update_trigger BEFORE UPDATE ON  My_table
>   FOR EACH ROW WHEN NEW.lastupdated < OLD. lastupdated
>   BEGIN
>   UPDATE song SET  lastupdated = DATETIME('now') WHERE id = NEW.id
>   END
>
> The WHEN clause is an attempt to avoid infinite recursion that UPDATE
> within an UPDATE would cause.

Typical UPDATE statements will leave this field with its old value, so
it might be a better ideas to use <= instead of <.

> However I get SQL errors when I try defining a trigger this way this
> in my favorite db dbrowser.

Thank you very much for keeping the error message secret.

When I fix the wrong table table name and add the missing semicolon after
the UPDATE statement, this trigger works fine.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users