2

So I want to push back the data that recently got inserted into my table to another server through REST API as soon as I get a new record into the SQL table. Because my client wants on the fly data updation to another server. I'm not sure what would be the best way to achieve it. I have following options, but can someone guide me what option should I opt that would fulfill my requirement.

  1. SQL Triggers (but how to call rest api in triggers, even if its possible then is it a good way?)
  2. SQL Jobs (SQL jobs are supposed to be scheduled at a specific time but I want the data to push at the same instant)
  3. Windows Service (when to call windows service? is it possible that I write my rest api calling code into windows service and run the service through sql triggers?)
  4. Console Application (during my research I got to know console application can be called through sql jobs, but how to call sql job when a new entry gets inserted into the table)

I'm quite confused as in what process will work for me and how can I use above mentioned items (or any other) together or individually in order to accomplish what I want to?

Any kind of help or suggestion would be greatly appreciated.

Shilpa Soni
  • 2,034
  • 4
  • 27
  • 38
  • I think triggers in this situation would be overkill, plus you may find it hard to call a REST API from the trigger directly. As long as you only need to update the API every minute or so, why not use a Windows service or even scheduled task? You could assign each row and auto number ID, then each run of your service remember the latest row. Eg if your last row ID was 100 query for anything above 100. – Terry Lennox Jan 21 '18 at 13:16
  • @TerryLennox Thanks for the response! but which event or control should I use in windows service? like I used filesystemwatcher lately that monitors a specific folder, so if I could get same sort of control that monitors sql table that would be great. – Shilpa Soni Jan 21 '18 at 13:21
  • I don't think you even need this, you can just have a loop that monitors your max ID number, this will be a very cheap query. There might be a latency of a few 10s of seconds, but this is surely acceptable. If the ID changes, just select the rows inserted since the last change and send them to your REST API. – Terry Lennox Jan 21 '18 at 13:24
  • @ShilpaSoni how did you overcome this issue? I seem to be in the same position as you. – exception_thrown Apr 25 '20 at 02:46
  • 1
    @PHPNoob Yes this is what I did, so I wrote my code of calling rest API in a console application then I created an SQL job and scheduled to run it in every one minute. This SQL job checks whether there is some data to push if yes then executes the console application. Hope this helps. – Shilpa Soni Apr 25 '20 at 07:00

0 Answers0