SQL Server 2008：我可以暂时暂停异步触发器的执行吗？
有没有办法暂时抑制 SQL Server 2008 中异步触发器的执行。我已经使用队列和从队列读取的服务代理实现了触发器。因为，我想在此服务器上执行一些繁重的查询，而这些触发器将大大减慢操作速度 - 我如何停止触发器的执行，但它们仍然进入队列以便稍后执行它们。
提前致谢！
Is there a way to temporally suppress the execution of asynchronous triggers in SQL Server 2008. I have implemented the triggers with a Queue and a Service broker that reads from the queue. Since, I want to execute some heavy queries on this server and those triggers will slow the operation drastically - how can I stop the execution of the triggers, but still they get into the queue in order to execute them later.
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
第一个答案是注释掉存储过程，然后禁用和启用队列应该可以工作。
我有类似的情况 - 插入更新触发后服务代理队列落后。
队列使用存储过程来激活。
我执行了以下操作（与第一个答案几乎相同，但我没有注释掉存储过程主体）：
1. 停用队列：
alterqueueQueueNamewithactivation(status=off);
进行批量更新，消息将保留在队列中。
激活队列：
alterqueueQueueNamewithactivation(status=on);
将为队列中每条保留的消息执行队列存储过程。
The first answer with commenting out the stored procedure and then disabling and enabling the queue should work.
I have similar situation – service broker queue behind after insert update trigger.
The queue uses stored procedure for activation.
I did the following (almost the same as the first answer but I do not comment out the stored procedure body):
1. Deactivate the queue:
alter queue QueueName with activation(status = off);
Do the mass updates, the messages will be preserved in the queue.
Activate the queue:
alter queue QueueName with activation(status = on);
The queue stored procedure will be executed for each of the preserved messages in the queue.
我们得出的结论是：
首先：注释读取队列中消息的存储过程的主体。其他一切（触发器、队列等）都在按其应有的方式运行。这样，消息就会进入队列，但没有人从那里读取
第二步：执行繁重的脚本。现在队列中充满了稍后将执行的操作。
第三：取消原始状态的存储过程主体的注释
第四：对队列执行Disable 和Enable 操作。现在存储过程将开始从队列中读取。并且更改是异步应用的。
Here what we came up with:
First: Comment the body of the stored procedure that reads the messages in the queue. Everything else (triggers, queue, etc.) is running as it should run. This way the messages go into the queue, but nobody reads from there
Second: Do the heavy lifting scripts. Now the queue is filling with operations that will be later executed.
Third: Uncomment the body of the stored procedure in its original state
Forth: Perform Disable and after Enable operation on the queue. Now the stored procedure will start to read from the queue. And the changes are applied asynchronously.
是的，禁用触发器，完成后启用触发器。请参阅 http://msdn.microsoft.com/en-us/library/ms189748。 aspx 获取文档。
但是这意味着触发器永远不会针对您输入的数据运行。如果您最终希望触发器执行的操作发生，则必须手动使其发生。请注意，这需要重复的逻辑，因此很容易搞砸。请谨慎使用。
Yes, DISABLE TRIGGER and when you're done ENABLE TRIGGER. See http://msdn.microsoft.com/en-us/library/ms189748.aspx for the documentation.
However this means that the triggers will never run for the data you put in. If you eventually want the stuff the triggers do to happen, you'll have to manually make it happen. Be aware that this requires duplicated logic, and therefore it is easy to mess it up. Use appropriate amounts of caution.