Adding a Table Trigger in SQL Server for a webhook

Question

A third-party application uses MS SQL Server as the data store. In the 3rd-party application API, they forgot to add webhooks for events so I'm trying to add the concept of webhooks in their API by adding a relevant table trigger on their SQL Server database.

The idea is to capture UPDATE events with a table trigger and, if they change a given field, then notify a process (outside the SQL Server box) to perform some batch actions.

I was thinking about having SQL Server somehow calling a URL (aka the "webhook") without waiting for a response (or timing out immediately), preferably with T-SQL.

Is there any way to do that in SQL Server (asyncronous URL call from T-SQL) or the only safe way to communicate with external systems without the risk of accidentally locking up SQL Server is to send an email from SQL Server? (I'm using SQL Server 2012)

Answer 1

Is there any way to do that in SQL Server (asyncronous URL call from T-SQL)

No. You must write to a table or Service Broker Queue, and have something asynchronous process the notification from that table or queue.

You can use a simple table for that with a polling process, or use Service Broker (Database Mail, Event Notifications, and Query Notifications all use Service Broker).

Service Broker Internal Activation enables you to register a stored procedure that will be run by a background process whenever there are messages in a queue. You can block this background process with long-running tasks without interfering with the application workload. You can also process the queued messages with an external application.