On Thursday, 12 March, 2020 09:37, David Blake <davebl...@kodi.tv> 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
