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

Answer 2

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.