Is it possible to create a trigger that invokes a web service?

Navigation
6

I'm working on the integration of two databases.

It is a system that needs the status of employees in HR.

For performance issues, I can not point all the employee-dependent queries to the HR base, what I did was create a functionality to import that data into my database.

I have now created a feature on my system that at certain time intervals fetches the data from the RH database.

But I'd like to change the implementation to something like that, when the data in the base changes, invoke a webService on my system that will import the data from RH, and update my database.

As we can only work on the database, not on the RH system, I thought it would be possible to create a trigger in the RH database that triggers my process on the other system.

Is it possible to create a trigger in SQL Server that invokes a webService?

    
asked by anonymous 20.01.2014 / 13:05

1 answer

3

Being only on merit in the question of invoking web service from a trigger, there is this article in English.

The logic is to use the sp_OAMethod procedure that lets you call a method from a OLE object. 

DECLARE @Param1 INT
DECLARE @obj INT
DECLARE @valorRetorno INT
DECLARE @url VARCHAR(200)
DECLARE @response VARCHAR(4000)

SET @Param1 = 35

SET @url = 'http://localhost/WebServices/Service1.asmx?Param1=' + CONVERT(VARCHAR, @Param1) + ''

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'Get', @url, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

SELECT @response AS Param1
EXEC sp_OADestroy @obj

The variable @response contains the return code of the GET call.

There are other ways, such as invoking DLLs that call web services. Other articles in English:

20.01.2014 / 14:30
Custom RoleProvider Name of the months in Portuguese and English