Thanks for your test !
There is no other way to force the device to reread the database without plug it in or shutdown it ? I was thinking about inserting a fake line in a thing as sensitive as content and deleting afterward.
If the update are done but not reread, what about the delete ? (I think about your trigger on analytic event) The whole process could be inverted : remove a image from Screensaver collection (and delete the line) for setting it as screensaver...
---
Random thoughts & questions : If the trigger was on update content, should the device read it?
If it’s the case, the datetime(now, -1 month) should do the trick with one trigger only.
create trigger add_screensaver
after update of DateLastRead, ReadStatus
on content
for each row
when new.ReadStatus = '2'
and new.ContentType = '6'
and new.ContentID like '%screensaver%'
BEGIN
update content
set DateLastRead = datetime('now', '+1 month'), ReadStatus = '1'
where ContentID = old.ContentID
-- = new.ContentID should work too.
and ContentType = '6';
END;
Only one trigger, but all the screensaver named and used images will stay at ReadStatus=1. The only way to remove an image, by this way, is to add (read) another one. Still, it depends how the image is processed when added the first time to the device. If there is only one insert and no update on the main content line, it’s OK. If not, it should mess with files adding on the device, hence my reluctance to use a trigger on content table. Nevertheless, all of this is dependent on the capability to reread the data...
EDIT : this one doesn’t work at all. the data in content does not even bother to update