20

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.

Jack Marchetti
  • 15,536
  • 14
  • 81
  • 117
  • 4
    Wrong question: can SQL call a web service at all? – John Saunders May 27 '11 at 14:05
  • 2
    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! – marc_s May 27 '11 at 14:05
  • 5
    I would 1) add an entry "do call the webservice later" to a table in your trigger (just INSERT - that's all) and then 2) have an asynchronous service (a SQL job, or a Windows NT Service) that makes those calls separately from the actual trigger execution – marc_s May 27 '11 at 14:09
  • 1
    @marc_s, agreed. You should post good answers as answers, not as comments! – Philip Kelley May 27 '11 at 14:14
  • It's generally considered bad form to put a lot of logic in triggers as it reduces the maintainability of your db. If (when) something eventually goes wrong in the web service it won't be obvious why your inserts are failing. – Phil Gan May 27 '11 at 14:19
  • @Phil - so what do you suggest? feel free to add an answer – Jack Marchetti May 27 '11 at 14:23
  • Does the logic have to be in the database? – Phil Gan May 27 '11 at 14:26
  • The logic isn't in the database. I just need something to call a web service after a row is inserted. Right now I have the actual "Insert" action method, calling the "GetUsers" method. That just didnt seem right to me. – Jack Marchetti May 27 '11 at 14:28
  • I'm guessing you have a "business" layer between your database and the rest of the code? I'd probably put the webservice call in there. – Phil Gan May 27 '11 at 14:43
  • No, no business layer really. I'm just using ADO.NET entity and from the ActionMethods I use LINQ to call the database. – Jack Marchetti May 27 '11 at 14:53
  • @Philip Kelley: your wish was my command - added my comments as an answer, and added some more info – marc_s May 27 '11 at 15:09
  • I needed exactly same thing, Can someone help me? How to call push service from stored procedure after insert. – mayank.karki Oct 22 '13 at 06:43

5 Answers5

27

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I think you're misunderstanding what I want the trigger to do. The trigger would just call the webservice - the webservice would then send data to a push server somewhere. I just don't know where I should call the method to send the data to the push server. – Jack Marchetti May 27 '11 at 15:13
  • @Jack Marchetti: as I say: do **not** let your trigger call a web service - that's a recipe for disaster. There must be another (and a better) way.... – marc_s May 27 '11 at 15:20
  • Gotcha I'll avoid that then. What I'll probably end up with is just a method which inserts the location, and then posts that data to the Apple notification service – Jack Marchetti May 27 '11 at 23:06
  • I have used SqlSependency to trigger database changes to show push notifications to client but recently we moved to SQL Azure and it doesnt support `SqlSependency` so Is there a better way than [this](http://stackoverflow.com/questions/9880091/monitor-data-changes-in-sql-azure) to to get notifications when SQL Azure data changes or when new data is inserted ? – Shaiju T Jul 20 '16 at 16:10
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.

Sunny Milenov
  • 21,990
  • 6
  • 80
  • 106
  • Would you suggest keeping it the way I have it? iPhone sends a push notifcation to "InsertLocation" method, and that method adds the location, then based off that location returns the users near them – Jack Marchetti May 27 '11 at 14:11
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.

Todd C
  • 29
  • 1
  • Please could you post your bat file contents? I'm trying to get started with this and need a way to get SQL to send notifications to web push subscriptions. I'm totally lost :-( Thanks – Matt Deemer Jun 29 '19 at 20:44
1

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.

ashes999
  • 9,925
  • 16
  • 73
  • 124
  • That's how I have it setup now. I have a method called "InsertLocation" which inserts the users location, then does a search for nearby users and returns them. – Jack Marchetti May 27 '11 at 14:08
0

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.

Dave
  • 1,823
  • 2
  • 16
  • 26