How to call REST API from a Trigger

Have you ever wondered how  you can call a REST API from a trigger in SQL server?  Here is how you can do it with SQL server stored procedure. You just need to create a trigger that executes the stored procedure and you are done.

Run the following query to enable OLE automation procedures.  After executing the  OLE automation queries create your stored procedure and give it a name such as webRequest. Create an insert/ update trigger on your table that executes the webRequest stored procedure.

Pass the post params as the inserted or updated values from your trigger.

	--This query enables ole automation procedures. set 0 to disable
	exec master.dbo.sp_configure 'Ole Automation Procedures', 1
	RECONFIGURE
	--OLE utomation disabled the following error is thrown.
	--SQL Server blocked access to procedure 'sys.sp_OACreate' of component
	--'Ole Automation Procedures' because this component is turned off as part
	--of the security configuration for this server.
	--A system administrator can enable the use of 'Ole Automation Procedures'
	--by using sp_configure. For more information about enabling 'Ole Automation Procedures',
	--search for 'Ole Automation Procedures' in SQL Server Books Online.
	--Running the above query may result to this erro
	--The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.
	--To fix this error run the query below and the enable OLE automation
	exec master.dbo.sp_configure 'show advanced options', 1
	RECONFIGURE

view raw enableOleAutomation.sql hosted with ❤ by GitHub

	--create/alter a stored proceudre accordingly
	create procedure webRequest
	as
	DECLARE @authHeader NVARCHAR(64);
	DECLARE @contentType NVARCHAR(64);
	DECLARE @postData NVARCHAR(2000);
	DECLARE @responseText NVARCHAR(2000);
	DECLARE @responseXML NVARCHAR(2000);
	DECLARE @ret INT;
	DECLARE @status NVARCHAR(32);
	DECLARE @statusText NVARCHAR(32);
	DECLARE @token INT;
	DECLARE @url NVARCHAR(256);
	DECLARE @Authorization NVARCHAR(200);
	--set your post params
	SET @authHeader = 'BASIC 0123456789ABCDEF0123456789ABCDEF';
	SET @contentType = 'application/x-www-form-urlencoded';
	SET @postData = 'KeyValue1=value1&KeyValue2=value2'
	SET @url = 'set your url end point here'
	-- Open the connection.
	EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
	IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
	-- Send the request.
	EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
	--set a custom header Authorization is the header key and VALUE is the value in the header
	EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'Authorization', 'VALUE'
	EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
	EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
	EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
	-- Handle the response.
	EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
	EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
	EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
	-- Show the response.
	PRINT 'Status: ' + @status + ' (' + @statusText + ')';
	PRINT 'Response text: ' + @responseText;
	-- Close the connection.
	EXEC @ret = sp_OADestroy @token;
	IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
	go

view raw webRequest.sql hosted with ❤ by GitHub