SQL队列，在未来某个日期执行SP
我正在使用 SQL Server 2005...
我想使用触发器来监视 table1 的更新。发生此更新时，我将检查另一个表（表 2），使用表 2 中的数据将一个项目放入队列中，以便在将来的可变日期执行。可能是从现在起 10 秒，或者 2 小时，这个日期是通过检查表 2 中的数据确定的。在处理放入队列中的项目之前，table1 的触发器有可能再次执行。在这种情况下，需要删除队列中的项目，并将新项目放入队列中。
流程：
table1被更新 table1 的触发器触发并查询 table2 以确定时间戳。该时间戳和代码将添加到队列中。有东西监视该队列并在确定的时间戳处执行代码。如果在从队列中检索项目之前更新了 table1，则该项目将被删除，并将新项目添加到队列中。
是否有一个消息传递服务可以监视日期并在该日期执行，或者我应该在每次触发此触发器时创建一个新的 sql 作业？或者还有其他选择吗？这需要可扩展。
我希望我能够解释我的问题，如果您有任何疑问，请告诉我。感谢您提供的任何帮助。
I am using SQL Server 2005...
I want to monitor table1 with a trigger for an update. On the occurrence of this update, I will check another table (table2), using the data from table2 I place an item in the queue to execute at a variable date in the future. Could be 10 seconds from now, or 2 hours, this date is determined from checking the data in table2. There is a chance the trigger for table1 could execute again before the item that was placed in the queue is processed. In this case, the item in the queue needs to be removed and a new item will be placed in the queue.
Process:
table1 is updated
table1's trigger fires and queries table2 to determine a timestamp. This timestamp and code are added to the queue. Something monitors this queue and executes the code at the determined timestamp. If table1 is updated before the item is retrieved from the queue, that item is removed and a new item added to the queue.
Is there is a messaging service out there that will monitor the a date and execute on that date or should I create a new sql job everytime this trigger is fired? Or are there other options? This needs to be scalable.
I hope I was able to explain my problem, if you have any questions let me know. Thanks for any help you can offer.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您是否看过SQL Server Service Broker？
Service Broker 教程
更新：您可以设置消息优先级，但我认为您无法在特定日期之前本地提取。除了根据定义，如果将两条消息放入队列中，最旧的消息将首先从队列中“弹出”，即它们按日期升序删除。
Have you looked at SQL Server Service Broker?
Service Broker Tutorials
UPDATE: You can set message priority, but I don't think you can natively extract by a specific date. Except that by definition, if you place two messages into a queue the oldest will 'pop' off the queue first, i.e. they are removed in ascending date order.
SQL-Server 代理作业通常用于在特定日期/时间执行任务。我认为可以使用 sp 创建或修改作业，因此您可以在触发器中使用它们。不利的一面是，可能存在一些权限问题。触发触发器的用户必须具有创建/修改作业的权限。
SQL-Server agent jobs are normally used to execute tasks at a specific date/time. I think it is possible to have a job created or modified with sp's, so you could use these in the trigger. On the down side, there may be some permission issues. The user who fires the trigger must have the permission to create/modify the job.