Does anyone know if I can run a SQL trigger in Node-Red to have it push information through the flow whenever the database is updated?
@Stylo as far as I know, SQLite triggers can only be used to trigger sql commands on the database - you cannot trigger external processes. So there's no graceful way of doing what you are looking for.
At a stretch you could setup an SQLite trigger that updates a 'last updated' column in a table and then have a NR flow periodically poll that table to see if something has changed. But that is pretty hacky - polling the table would be a lot of wasted work if your updates are few and far between. It would also depend how quickly you'd need the flow to be triggered as to how often you poll and how much processor time you waste doing so.
you could also try to use a filesystem watcher to monitor the sqlite database file...
1 Like
In theory, sqlite does have a data change notification api but I can't find evidence of anyone implementing it in Node.JS.
However, I think that only works from updates on the same connection anyway so probably isn't really any help.
Much better would be to either use a more suitable database such as CouchDB, REDIS.
Or rethink how the db is updated. Perhaps via MQTT messages.
1 Like
