Maintaing a Last updated timestamp column using triggers

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Maintaing a Last updated timestamp column using triggers

David Blake
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

The WHEN clause is an attempt to avoid infinite recursion that UPDATE
within an UPDATE would cause. However I get SQL errors when I try defining
a trigger this way this in my favorite db dbrowser.

Maybe using triggers for this is flawed, and I should just ensure all
update statements set the column, or am I missing a trick?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maintaing a Last updated timestamp column using triggers

Jean-Luc Hainaut
In reply to this post by David Blake
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maintaing a Last updated timestamp column using triggers

David Raymond
In reply to this post by David Blake
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maintaing a Last updated timestamp column using triggers

Simon Slavin-3
In reply to this post by David Blake
On 12 Mar 2020, at 3:36pm, David Blake <[hidden email]> 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.

<https://sqlite.org/pragma.html#pragma_recursive_triggers>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maintaing a Last updated timestamp column using triggers

Keith Medcalf
In reply to this post by David Blake

On Thursday, 12 March, 2020 09:37, David Blake <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maintaing a Last updated timestamp column using triggers

Keith Medcalf
In reply to this post by David Blake
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users