what's the best practice for calling a webservice from within a trigger (SQL Server)

Question

I have need to call a webservice when a particular column in a table changes. I can handle the deciding of which column changed in the insert/update/delete but I'm not sure how I should call the webservice.

The webservice is offsite, we have a little control over it (we can ask for changes and get a reasonably quick response) but the service is a work in progress and so is our code.

SQL Server version is 2005.

Update: much of the business logic in this app is in the SQL layer. No way for me to change it and complaining about it is not a good use of my time. It is what it is. Wish that there was a business logic layer but it's partially in the pres layer and partially in the SQL layer. A trigger in the table is the most central place.

Answer 1

Don't. The overhead of a web service is incompatible with the execution processing cycles of a database. Take it out of the database and (preferably) make it asynchronous - see references to "queue/monitor" in various answers below.

Probably a Duplicate question, too.

Answer 2

Have the trigger insert a row in a table with whatever data the web service needs, then have an outside process watch that table and call the web service when a new row appears.

Answer 3

A CLR function can make web service request and process the response. But this your case, you might want to create a CLR function and invoke the function somewhere within your SQL objects.

I'd suggest to make the web service call in a high layer rather than the database level. for example, the data access layer in your code which modified that column.

Answer 4

Most shops close port 80 on the SQL box

Also a trigger should be mean and lean and returns as fast as possible, you don't want to call external things from a trigger

Dump the data from within a trigger into another table then write another process that checks that table and then does the web service call

Answer 5

I would just re-iterate what has been said already. I have worked in situations where this has been implemented and it is a nightmare.

you will end up with a very un-responsive database as rows will be locked whilst the trigger executes the call to the web service. So if the web service is slow or network latency is high you will have long running transactions locking rows and reducing the concurrency of your database.

The site where I saw this implementation was changed to do the same as moose describes and the whole application gained a huge boost as the database concurrency was increased dramatically.