Quite simply I have a SQL database with a .net front end and I need to set off event's when an specific time is reached . I call them alarms , so a user can set an alarm for say 12:30am on x day and when that time is reached on x day a popup would appear reminding them.
What is the best way to achieve this , should I poll the database or try to use SQL triggers?. I have not attempted this before so , if anyone has experience could you shed some light on the best approach? and any potholes that I should try to avoid falling into !.
Any insight = great
Yes sorry triggers where in my head for another project (lack of sleep).
To clarify , Multiple clients may be connected (usually 2 will always be on ,but it might be the case that everything goes down and none are connected) So I will need to think about how the alarms/popups happen. When they receive a popup alarm , they can choose to dismiss or "action" it , to say they have done the task.
Depending on the information on the alarm , different users should be notified i.e some people won't get the alarm if chosen , (hopefully achieve this through active directory logins and keep a table of current users & assign each to an area so people will only see alarms in there area, while others may just receive all )
Thanks for all the input : It sounds like I will want a central program or service?.
I could have a service that keeps polling the database checking alarm times and current active users, if found it updates a notification table which the user pages polls for every couple of seconds(10)(Clarify: The alarm times , should be put in well in advance and the alarm notification does not need to be seconds accurate). How does that sound?.
In terms of load , I can't see more than around 15-20 using it at the one time but usually only around 5-6.
Any glaringly obvious omissions or concerns?.
I don't think that you understand triggers correctly. They fire off based on actions taken (for example, a row is inserted into a table). They are not time-based. I don't think that this is what you're looking for based on your explanation.
Who will be seeing these alarms? Will they literally be pop-up windows on a client machine? If a pop-up appears in the woods and no one is there to read it, was there still an alarm?
My first though, given the limited information, would be to have the client load any alarm data when it is first opened. If any alarms were missed (i.e. they should have gone off when the person wasn't logged into the system) then they would pop up immediately (preferably with some visual cue that they were in the past). Any other alarms that are scheduled in the near future can then be set to fire off at the appropriate time.
The application could repoll the server every so often (once a minute? once every 10 minutes?) to see if there are any new alarms set in the system. The downside here is that if someone goes in and creates an alarm for the immediate time then it might not pop-up until the next poll of the database. This will help you determine the business rules on how often the polling needs to be done.
Some additional notes on this approach... you'll need to mark alarms as having been "handled" so that they don't pop up again. Also, you need to make sure that the clients' clocks are synchrponized with the server. Plus, do you need to consider differing time zones?
Another possible approach would be to have a central server that is polling the database and pushes out alarms to the clients. This can save each of the clients from having to constantly poll the database (you only have one poller), but it won't catch "missed" alarms. If you always have a bunch of clients connected and need to send out "worldwide" alerts then this might be the route. If alarms are user-specific then I'd go with the first approach.