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

Trigger->Queue->SP->XP_XMDShell->BAT->cURL->3rd party web service

I used a trigger to insert a record in a Queue table, then a Stored procedure using a cursor to pull Queued entries off.

I had no WSDL or access to the 3rd party API developers and an urgent need to complete a prototype, so the Stored Procedure calls XP_CMDShell calling a .bat file with parameters.

The bat file calls cURL which manages the REST/JSON call and response.

It was free, quick and works reliably. Not architecturally pure but got the prototype off the ground.

Answer 2

What about a stored procedure? Instead of setting it up on a trigger, call a stored procedure, which will both insert the data, and possibly do something else.

As far as I know, triggers are pretty limited in their scope of what they can do. A stored procedure may have more scope (or maybe not).

In the worst case, you can always build your own "API" page; instead of directly inserting the data, request the API page, which can both insert the data and do the push notification.

Answer 3

A good practice is to have that web page make an entry into another table (i will call message_queue ) when the user hits the page.

Then have a windows service / *nix daemon on a server scan the message_queue table and perform the pushes via a web service to the mobile app. You can leverage the power of transaction processing in SQL to manage the queue processing.

The nice thing about this approach is you can start with everything on 1 stand alone server, and even separate the website, database, service/daemon onto different physical servers or server clusters as you scale up.

Answer 4

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

Answer 5

It depends on the business needs. Usually I would stay away from using triggers for that, as this is a business logic, and should be handled by the BL.

But the answer is Yes to your question - you can do that, just make sure to call the web service asynchronously, so it does not delay the insert while the web service call finishes.

You may also consider using OneWay web service - i.e. fire and forget.

But, as others pointed out - you are always better off not using trigger.

If properly architectured, there should be only one piece of code, which can communicate with the database, i.e. some abstraction of the DAL in only a single service. Hook there to make whatever is needed after an insert.

I would go with a trigger, if there are many different applications which can write in the database with a direct access to the database, not trough a DAL service. Which again is a disaster waiting to happen.

Another situation, in which I may go with a trigger, if I have to deal with internally hosted third party application, i.e. if I have access to the database server itself, but not to the code which writes in the database.