Share via

trigger to call URL

  • Article

Question

Thursday, December 18, 2014 4:55 PM

I am writing to seek help, if it is possible to create a insert trigger, for calling a url for example google.com.  so the trigger would call the google.com url, when a new data record is inserted in the employee table.

please advice further, if possible

Thank you

All replies (4)

Friday, December 19, 2014 9:34 AM ✅Answered

Hi missy786,

According to your description, you want to create an insert trigger for calling a URL in SQL Server.

Technically it is possible to call a URL in Triggers, but invoking URL in Trigger is not recommended. As triggers are not asynchronous, transaction will wait till the trigger completes, so when the Web request takes more time than expected and our transaction will keep waiting until the request completes.

In this situation, if you use SQL Server 2005 or later versions, using Service Broker queues may be a good practice. For more information about this, please refer to the following similar thread: https://social.technet.microsoft.com/Forums/en-US/4fb59f97-c6f0-45cf-8636-26f0ab5ca8e9/invoking-url-in-trigger?forum=transactsql

If you really need to call a URL in Triggers, the following sample query is for your reference:

Declare
  @obj   int
,@sUrl varchar(200)
,@response varchar(8000)

set @sUrl = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT' --Your Web Url (invoked)

   exec sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
   exec sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
   exec sp_OAMethod @obj, 'send'
   exec sp_OAGetProperty @obj, 'responseText', @response OUT

   exec sp_OADestroy @obj

select @response [response]

If you have any question, please feel free to let me know.

Regards,
Jerry Li

Friday, December 19, 2014 10:23 AM ✅Answered

What is the usage of this? May be its something to be done in the application code, not in the SQL. Could you explain the business case here?

Satheesh
My Blog | How to ask questions in technical forum

Friday, December 19, 2014 12:58 PM

I am writing to seek help, if it is possible to create a insert trigger, for calling a url for example google.com.  so the trigger would call the google.com url, when a new data record is inserted in the employee table.

please advice further, if possible

Thank you

Friday, December 19, 2014 1:03 PM

Please review your previous post before you ask the same question again.

Olaf Helper

[ Blog] [ Xing] [ MVP]