# Request for Best Practice: triggering flows based on new entries in a database
**URL:** https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960
**Category:** General
**Tags:** database
**Created:** [11 July 2022 16:55 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960 "2022-07-11T16:55:18Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Taylor](https://discourse.nodered.org/u/Taylor)
#### Post date: [11 July 2022 16:55 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/1 "2022-07-11T16:55:18Z")
I've been using new entries in a database to initiate flows in my project but I'm afraid my current strategy is a silly workaround due to my lack of knowledge/experience. I'm looking for advice on how to improve my flow trigger for efficiency and any future editor's sanity.
My current strategy is to run this SQL Query every 0.25s:
> SELECT Top 1 \* FROM Table1 ORDER BY ID DESC
Followed by a filter that only allows unique payloads through, which then leads to my code.
Is there a better way to achieve the same results? Thanks in advance!
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [11 July 2022 17:03 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/2 "2022-07-11T17:03:26Z")
> [@Taylor](#):
>
> Is there a better way to achieve the same results? Thanks in advance!
Probably not (you dont say which flavour SQL nor which contrib node you are using) but most node-red DB nodes do not support "triggers" whicjh I would suggest is the "best" way.
That said, how does the data get into that table? Could the thing that puts data in the table fire an event (like MQTT)?
If you are using mySQL or MSSQL (and others) you could run some app/script `ON TRIGGER` to signal to node-red something has changed
Lastly, is it _REALLY_ necessary to check every 0.25s? that is asking a bit too much IMO (if you get an outage, that will end up spamming and potentially crashing node-red) - surely 5 sec or 2 secs would be hardly noticeable?
---
### Author:  [@Taylor](https://discourse.nodered.org/u/Taylor)
#### Post date: [11 July 2022 18:24 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/3 "2022-07-11T18:24:46Z")
Hey Steve, thanks for the response. I've seen you everywhere on this forum and have followed your advice countless times which actually helped me complete this project in the first place!
> you dont say which flavour SQL nor which contrib node you are using
I'm using `node-red-contrib-mssql-plus` for my SQL queries from a Microsoft SQL server.
> That said, how does the data get into that table? Could the thing that puts data in the table fire an event (like MQTT)?
Data enters the table via an antiquated python program made years ago by an ex-employee. My IT team hasn't been able to push any updates to that program since. Otherwise I agree, an MQTT publish alongside the database insert would be ideal. If that's truly the best solution then I may have to contract this to the original coder.
> If you are using mySQL or MSSQL (and others) you could run some app/script `ON TRIGGER` to signal to node-red something has changed
I'll look into opportunities using the `ON TRIGGER` functionality. We've recently lost our database expert so that will be uncharted territories for me, but it sounds like a good alternative. I've done a quick google search on a "MSSQL TRIGGER ON INSERT into Node Red" and all the solutions I've seen require middleware to accomplish this. I also didn't find any nodes that can accomplish this unfortunately. I was hoping to keep the coding between the SQL server and Node red, potentially using MQTT to pass info since that's already set up.
> Lastly, is it _REALLY_ necessary to check every 0.25s? that is asking a bit too much IMO (if you get an outage, that will end up spamming and potentially crashing node-red) - surely 5 sec or 2 secs would be hardly noticeable?
The data updates are based off of users scanning bar codes, often in quick succession. We've had instances where several entries and been inserted in less than 1s, causing some to be missed. The quick fix was to up the poll frequency.
I'm positive it's possible to grab additional recent entries less frequently and perform the tasks on each one, but not without overhauling a fair bit of coding. That led me to this post, wondering how terrible is my current strategy and if it's worth it or even vital to put the time into updating my strategies before it's replicated onto future projects.
So far Node-RED has been able to handle the frequency. The MSSQL nodes themselves HAVE crashed a couple times where I had to re-initiate the connection.
From what I can tell, the ideal solution seems to lie between Node-RED and the SQL server which means it's unfortunately no longer a question for this forum unless you have other ideas.
Thank you again
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 July 2022 19:32 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/4 "2022-07-11T19:32:40Z")
This may be of help: [Call a DLL or EXE file from SQL Trigger (microsoft.com)](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/688ec345-813e-4028-a7b1-1839e4af9d7b/call-a-dll-or-exe-file-from-sql-trigger?forum=sqlnetfx)
---
### Author:  [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [11 July 2022 19:49 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/5 "2022-07-11T19:49:12Z")
So maybe call mosquitto\_pub ? To create a node-red trigger on demand ?
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [11 July 2022 20:20 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/6 "2022-07-11T20:20:19Z")
IMHO, the better solution would be to connect the barcode scanner to node-red, then you will have an instant event that you can use to (for example) perform barcode value sanitisation - only then, once verified, perform your operations and then update the database.
Scanning the DB at 0.25s is asking for problems - any hickup can make things unpleasant. TBH, its not _really_ a frequency issue, just that everything (scanner, database polling, node-red) is currently "async" and not really interlocked (working on chance) - if you know what I mean.
Hope that helps in some way.
PS, time to tell the bosses to hire a DB admin
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [25 July 2022 20:20 UTC](https://discourse.nodered.org/t/request-for-best-practice-triggering-flows-based-on-new-entries-in-a-database/64960/7 "2022-07-25T20:20:27Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.