Call Web Services from SQL Trigger

Table of Content

When maintain some existed systems, we need to sync old database with new database from other system without touching code.

In order to do that, we may need to call WebServices directly from SQL Server to update content through API everytime database change or create.

In this tutorial, i will show you how we can do that with SQL server and RestFul Webservices.

First lets make one example table on SQL server 2008 R2 which we name as “Persons” with only 2 fields : “ID” and “Name”.

Each time have a row be updated or inserted, we need to fire an WebServices POST request to http://example.com/update.

Please take a note that, we only can call Web Request in SQL through a Procedure but not a Trigger and this also not good securities and clean solution.

But ofcourse, somewhere and sometime, we must doing like that.

To start with our solution, i will go step by step :

Step 1

We need to configure SQL Server allow Web Request call by running command below :

Step 2

Create Procedure that will call WebService Request with our Row Content :

In order to call WebServices with Parameters, as you see above, i built a string which contain all parameters, and then pass to Web Request through :

The vResponseText and vStatusText only using to debug from WebService call.

Step 3

Now we create a Trigger on our Table which will be fire each time table’s row updated or inserted :

Now try to edit or update our table records and check the log in our WebService API.

Leave a Reply

Your email address will not be published. Required fields are marked *