Use a POST method in a MSSQL trigger

Question

I need to POST (HTTP method) some info to an external URL when a trigger executes. I know there are a lot of security and performance implications when using triggers, so I am afraid this is not the place to do this kind of processing. But anyway I am posting this to get some feedback or ideas on how to approach the problem. Some considerations :

• The transaction fired in the trigger could be asynchronous.
• The process must take care of the authorization
• The end URL is a php script on the internet.

What really triggers this execution should be an insert or an update of one record to a table, so I must use this trigger since I can't touch the (third party) application.

On a side note, could the Service Broker be something to consider ? Any ideas will be welcome.

Answer 1

You are right, this is not something you want to do in a trigger. The last thing you want in your application is to introduce the latency of a HTTP request in every update/insert/delete, which will be very visible even when things work well. But when things work bad, it will work very bad: the added coupling will cause your application to fail when the HTTP resource has availability problems, and even worse is the correctness issues related to rollbacks (your transaction that executed the trigger may rollback, but the HTTP call is already made).

This is why is paramount to introduce a layer that decouples the trigger from the HTTP call, and this is done via a queue. Whether is a table used as a queue, or a Service Broker queue, or even an MSMQ queue is up to you to make the call. The simplest solution is to use a table as a queue:

• the trigger enqueues (inserts) a request for the HTTP call to be made
• after the transaction that run the trigger commits, the request is available to dequeue
• an external application that monitors (polls) the queue picks up the request and places the HTTP call

The advantage of Service Broker over custom tables-as-queues is Internal Activation, which would allow your HTTP handling code to run on-demand when there are items to be processed in the queue, instead of polling. But making the HTTP call from inside the engine, via SQLCLR, is something quite ill advised. An external process is much better for accessing something like HTTP and therefore the added complexity of Service Broker is not warranted.