sql server 2005 - Can a SQL trigger call a web service?

Question

I'm building out a RESTful API for an iPhone app.

When a user "checks-in" [Inserts new row into a table] I want to then take data from that insert and call a web service, which would send push notifications based upon that insert.

The only way I can think of doing this is either doing it through a trigger, or having the actual insert method, upon successful insert, call the web service. That seems like a bad idea to me.

Was wondering if you had any thoughts on this or if there was a better approach that I haven't thought of.

Answer 1

Even if it technically could, it's really not a good idea! A trigger should be very lean, and it should definitely not involve a lengthy operation (which a webservice call definitely is)! Rethink your architecture - there should be a better way to do this!

My recommendation would be to separate the task of "noticing" that you need to call the webservice, in your trigger, from the actual execution of that web service call.

Something like:

1. in your trigger code, insert a "do call the webservice later" into a table (just the INSERT to keep it lean and fast - that's all)

2. have an asynchronous service (a SQL job, or preferably a Windows NT Service) that makes those calls separately from the actual trigger execution and stores any data retrieved from that web service into the appropriate tables in your database.

A trigger is a very finicky thing - it should always be very quick, very lean - do an INSERT or two at most - and by all means avoid cursors in triggers, or other lengthy operations (like a web service call)

Brent Ozar has a great webcast (presented at SQL PASS) on The Top 10 Developer Mistakes That Don't Scale and triggers are the first thing he puts his focus on! Highly recommended