I'm working on an item management system, in which I need to constantly monitor the quantity of items available. Upon reaching a pre-set minimum threshold, the system should issue the item into an alert list for it to be reset.
What would be the best way to do this monitoring? A cron task running every n seconds or a trigger that will fire every time the amount is changed?
A tigger is the only valid option if you want accurate notification . Something that runs from time to time may work in some very specific scenarios, but "always" will perform less. Unless what you want is not well checked from time to time and the update cycle is very high, which does not seem to be the case.
The trigger ensures that any change in the bank notifies you. You might want this. You may wish to have notifications from time to time. They are different semantics.
The trigger has a cost for each operation; if you do not need these notifications on time, it may be a cost.
Check lots has an extra single cost and will make unnecessary operations, but relieves each update and can choose to do so in non-critical hours. It will not change much, but in large volumes it can make a difference. Can you leave the notification for later without any problems? Are you going to do this often? So I doubt it's a good option.
Take a test to see which works best for you. It is not easy to choose, depends on the need and how to implement. The danger is in the detail.
This site here chooses to update certain things in batch, but some are made on time. It depends on what you want and how much it affects the established performance metric.
In some databases better options than using cron .
Of course, it depends on the architecture of your solution. I'm considering what you want to do in the database. In general I'd rather do it on another layer.
If you have a system, it is likely that this system has a layer of services or a layer of data access or business rules that handle the transactions that the system is running that prevent direct access to the database.
If this is your case then it would be best for your item transaction to check this threshold when updating the quantity and triggering a message using the system messaging engine to generate this alert.
If the system has no layers and allows direct access to the database by any application then the trigger option will guarantee faster recognition of this limit condition, however triggers can affect the performance of the database.
The "cron" option you are evaluating is the passive way in which you run a query at each "X" time. It has the disadvantage that you have to check all items and this will generate a more comprehensive query in the bank. If the search interval is small, it can also cause performance problems. This option would be interesting if you had a separate system table to make these recurring queries.
I prefer the option to do in the transaction of the item itself because then you would have some advantages of trigger , which is to act when the change occurs in each item, and to be able to use the application messaging mechanism to act asynchronously with this rule and thus not affect the performance of transactions. You would also have the independent function of the database.
The downside of choosing the transaction is if you have the rules scattered throughout the system. In this case the best option will be trigger .
In this SO response you have some alternatives for using triggers in database when you want to implement something like a Observer .
