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 :

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Ole Automation Procedures', 1;
    GO
    RECONFIGURE;
    GO

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE;

Step 2

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

     ALTER PROCEDURE [dbo].[spSelect_CallWebPage] 
    (   
     @ID nvarchar(50),
     @Name nvarchar(50)     
    )
    AS

    DECLARE  @vPointer INT,  
    @vResponseText VARCHAR(8000), 
    @vStatus INT, 
    @vStatusText VARCHAR(200),
    @Parametres nvarchar(3000)
    -- Must be change on live.

    SET @Parametres = ''


    SET @Parametres = @Parametres + '&amp;id=' + @ID
    SET @Parametres = @Parametres + '&amp;name=' + @Name            

    EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT
    EXEC sp_OAMethod @vPointer, 'open',NULL, 'POST', 'http://example.com/update', 0
    EXEC sp_OAMethod @vPointer, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC sp_OAMethod @vPointer, 'send', NULL, @Parametres

    EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
    EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
    EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT               
    EXEC sp_OADestroy @vPointer

ALTER PROCEDURE [dbo].[spSelect_CallWebPage]

(

@ID nvarchar(50),

@Name nvarchar(50)

)

DECLARE @vPointer INT,

@vResponseText VARCHAR(8000),

@vStatus INT,

@vStatusText VARCHAR(200),

@Parametres nvarchar(3000)

-- Must be change on live.

SET @Parametres = ''

SET @Parametres = @Parametres + '&id=' + @ID

SET @Parametres = @Parametres + '&name=' + @Name

EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT

EXEC sp_OAMethod @vPointer, 'open',NULL, 'POST', 'http://example.com/update', 0

EXEC sp_OAMethod @vPointer, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'

EXEC sp_OAMethod @vPointer, 'send', NULL, @Parametres

EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT

EXEC sp_OADestroy @vPointer

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 :

    EXEC sp_OAMethod @vPointer, 'send', NULL, @Parametres

EXEC sp_OAMethod @vPointer, 'send', NULL, @Parametres

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 :

ALTER TRIGGER [dbo].[ModifyHreProfile] ON [dbo].[Persons]
    AFTER INSERT, UPDATE
    AS 
    BEGIN      
    DECLARE @ID nvarchar(50);   
    DECLARE @Name nvarchar(50);         

    -- get the information of the record inserted or updated 
    SELECT 
                 @ID = INSERTED.ID,        
                 @Name = INSERTED.Name

     FROM INSERTED 

    -- Call Procedure here

    EXEC spSelect_CallWebPage @ID, @Name
    END

ALTER TRIGGER [dbo].[ModifyHreProfile] ON [dbo].[Persons]

AFTER INSERT, UPDATE

BEGIN

DECLARE @ID nvarchar(50);

DECLARE @Name nvarchar(50);

-- get the information of the record inserted or updated

SELECT

@ID = INSERTED.ID,

@Name = INSERTED.Name

FROM INSERTED

-- Call Procedure here

EXEC spSelect_CallWebPage @ID, @Name

END

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

Step 1

Step 2

Step 3