在 SQL 数据库上触发事件的最佳方法
很简单，我有一个带有 .net 前端的 SQL 数据库，我需要在到达特定时间时触发事件。我将它们称为“闹钟”，因此用户可以设置一个闹钟，例如在 x 天的中午 12:30，当 x 天到达该时间时，会出现一个弹出窗口来提醒他们。
实现此目的的最佳方法是什么？我应该轮询数据库还是尝试使用 SQL 触发器？ 我以前没有尝试过，所以，如果有人有经验，你能透露一下最好的方法吗？以及我应该尽量避免掉进的任何坑洼！
任何洞察力=很棒
是的抱歉触发了我的另一个项目（缺乏睡眠）。
为了澄清，可能会连接多个客户端（通常有 2 个客户端始终处于打开状态，但也可能出现所有设备都出现故障并且没有连接的情况），因此我需要考虑警报/弹出窗口是如何发生的。当他们收到弹出警报时，他们可以选择忽略或“执行”警报，以表示他们已完成任务。
根据警报上的信息，应通知不同的用户，即如果选择某些人将不会收到警报，（希望通过活动目录登录来实现这一点，并保留当前用户的表并将每个用户分配到一个区域，以便人们可以只能看到该区域的警报，而其他区域可能只会收到所有警报）
感谢您的所有意见：听起来我想要一个中央程序或服务？
我可以有一个服务不断轮询数据库检查警报时间和当前活动用户，如果发现它会更新用户页面每隔几秒轮询一次的通知表（10）（澄清：警报时间应该放在很好的位置）提前，警报通知不需要精确到秒）。听起来怎么样？
就负载而言，我看不到一次使用它的数量不会超过 15-20 左右，但通常只有 5-6 左右。
有任何明显的遗漏或担忧吗？
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?.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为您没有正确理解触发器。它们根据所采取的操作触发（例如，将一行插入表中）。它们不是基于时间的。根据您的解释，我认为这不是您正在寻找的内容。
谁会看到这些警报？它们实际上是客户端计算机上的弹出窗口吗？如果树林里出现了一个弹出窗口，而没有人在场，那还有警报吗？
不过，鉴于信息有限，我的第一个想法是让客户端在首次打开时加载任何警报数据。如果错过了任何警报（即，当该人未登录系统时，警报应该响起），那么它们会立即弹出（最好带有一些视觉提示，表明它们是过去的）。然后可以将安排在不久的将来的任何其他警报设置为在适当的时间触发。
应用程序可以每隔一段时间（一分钟一次？每 10 分钟一次？）重新轮询服务器，以查看系统中是否设置了任何新的警报。这里的缺点是，如果有人进入并立即创建警报，那么它可能不会弹出，直到下次轮询数据库。这将帮助您确定有关需要进行轮询的频率的业务规则。
关于此方法的一些附加说明...您需要将警报标记为已“处理”，以便它们不会再次弹出。此外，您还需要确保客户端的时钟与服务器同步。另外，您需要考虑不同的时区吗？
另一种可能的方法是使用一个中央服务器来轮询数据库并向客户端发出警报。这可以使每个客户端不必不断轮询数据库（您只有一个轮询器），但它不会捕获“错过的”警报。如果您总是有一堆客户端连接并且需要发送“全球”警报，那么这可能就是路线。如果警报是特定于用户的，那么我会采用第一种方法。
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.
当数据库中发生某些事情时，触发器就会被激活。
我过去使用过以下所有内容...
1。使用外部应用程序
如果有应用程序需要了解事件，请在应用程序中为事件计时。
如果有多个客户端，您需要协调它们。要么使用中央主应用程序，要么使用某些同步过程。
2.代理
MS SQL Server 能够设置时间并重复触发某些 SQL 的事件。
如果没有需要触发此事件的外部客户端应用程序，这些将非常有用。
如果您有多个客户端应用程序，则类似于选项 1，但 SQL Server 成为中央应用程序，客户端轮询服务器以了解发生了什么情况。
3.无限循环
代替（或同时）代理，您可以编写一个永无止境的循环，并使用 WAIT 类型命令暂停，直到下一个事件到期。
注意
什么是最好的取决于您实际上要对这些事件做什么。轮询不是问题，但通常最好有一个单点控制事件的触发。
请注意，您（通常）不希望客户端到 SQL Server 的连接永远保持打开状态。而且您无法打开从 SQL Server 到一堆客户端应用程序的连接。因此，无论如何，应用程序都需要轮询服务器。但您可以对民意调查保持明智。
Triggers are activated when something happens in the database.
I've used all of the following in the past...
1. Using an external application
If there is an app that needs to know about the event, time the event in the App.
If there are multiple clients, you'll need to co-ordinate them. Either with a central master application, or some synchronisation process.
2. Agents
MS SQL Server has the ability to set up time and repeating events that fire off some SQL.
These are useful if there isn't an external client application that needs this event to be fired.
If you have multiple client applications, it's like option 1, but SQL Server becomes the central app and the clients poll the server to know what is going on.
3. Infinite Loop
Instead (or as well as) Agents, you could write a never ending loop and use WAIT type commands to pause until the next event is due.
NOTE
What is best depends on what you are actually going to do with these events. Polling is not a problem, but it is generally best to have a single point controlling the firing of events.
Do note, you don't (usually) want to have connections from a client to a SQL Server standing open forever. And you can't open a connection form SQL Server to a bunch of client apps. So the apps will need to poll the server regardless. But you can be intelligent about the polling.
一种方法是客户端始终监听 DATBASE，并在数据库中发生更改时在本地引发事件。 这是我对此的看法
编辑：
如果你想延迟，还有一个很棒的 SQL 命令 waitfor为未来执行某事
One of approach will be to client listening DATBASE all time and to rise event locally when something changed in database. Here is my SO about that
EDIT:
Also there is great SQL command waitfor if you want to delay executing something for future