如何从SQL Server触发器调用静态服务 [英] How to call restful service from SQL Server trigger
问题描述
我需要从SQL Server数据库表中的插入事件中使用单个参数调用一个宁静的Web服务。我知道这不是理想的设计，但我无法控制任何一个端点。在最大负载下，此功能的使用速度不会超过100个事件/分钟。
I need to call a restful web service with a single parameter from the insert event in a SQL Server database table. I know this is not ideal design but I don't have control of either end point. The usage of this functionality will not exceed 100 events/min at max load.
我对其他想法持开放态度，但我想到的两个选择是： p>
I'm open to other ideas but the two options I came up with were;
- 插入触发器-服务代理-.NET Windows Service调用Web服务
- C＃CLR触发器调用直接提供宁静的服务
我一直在研究选项2，并在Visual Studio中创建了一个SQL CLR触发器项目，但是在我添加了对 System.Web 该项目将不会生成，也没有生成错误，而只是在输出窗口中说 build failed。
I have been working on option 2 and created a SQL CLR trigger project in Visual Studio but after I add the references to System.Web the project will not build and there are no build errors, vs just says "build failed" in the output window.
在CLR触发器中可以使用哪些CLR库是否有一些限制？
Is there some restriction about which CLR libraries can be used in a CLR trigger?
使用服务代理因为我只与服务经纪人一起完成了一个项目，并发现很难实施。
Using the service broker scares me since I have only worked on one project with the service broker and found it very difficult to implement.
关于如何从触发事件中调用Web服务的任何想法
Any ideas on how to call the web service from the trigger event would be greatly appreciated.
推荐答案
触发器在导致其触发的语句的上下文和范围中运行-这意味着，直到触发器完全运行，该语句才能完成。
A trigger is run in the context and scope of the statement that causes it to fire - this means, that statement isn't going to complete until the trigger is completely run.
触发器应该非常灵活-小型且快速。永远不要从触发器调用外部服务，不要在触发器中包含游标，也不要在触发器中进行繁重的工作或冗长的计算。
A trigger should be very nimble - small and fast. You should never ever call external services from a trigger, you should not include cursors in triggers, you should not do any heavy lifting or lengthy calculations in a trigger.
我真的必须做这样的事情，我建议一种方法：
If you really must do something like this, I'd recommend an approach that:
-
请参阅触发器将一些值放入命令表-长时间运行的进程完成其工作所需的值
see the trigger just put a few values into a "Command" table - those values that the long-running process will need to complete its work
具有一个解耦的独立进程（例如，存储过程）或其他内容）将定期检查命令表中是否有新任务要完成-可以使用SQL Server代理作业在SQL Server中完成
have a de-coupled, separate process (e.g. a stored procedure or something) that will check that "Command" table periodically for new tasks to complete - this can be done in SQL Server using a SQL Server Agent Job
然后，已解耦的进程从命令表中获取信息，它是否起作用，并更新数据库（如有必要）
the decoupled process then grabs the information from the "Command" table, does it's work, and updates the database (if necessary)
这样，您的触发器很小巧，可以快速完成，因此不会减慢您的主进程/主系统的速度。冗长的过程是分离的，独立的，并且可以以最有意义的方式实现（SQL Server中的存储过程，或单独的独立的命令行工具或其他有意义的方法）。
This way, your trigger is small and nimble and completes quickly, thus not slowing down your main process / main system. The lengthy process is decoupled, standalone, and can be implemented in whichever way makes most sense (stored procedure inside SQL Server, or a separate standalone e.g. command-line tool or whatever makes sense).
这篇关于如何从SQL Server触发器调用静态服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
