I have this stored procedure which makes some GET request:

CREATE procedure [dbo].[alert_open_ordr](
@order_number varchar(max)
DECLARE @status int
DECLARE @responseText as table(responseText nvarchar(max))
DECLARE @res as Int;
DECLARE @url as nvarchar(1000) = '' + @order_number
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT
EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false'
EXEC sp_OAMethod @res, 'send'
EXEC sp_OAGetProperty @res, 'status', @status OUT
INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText'
EXEC sp_OADestroy @res
SELECT @status, responseText FROM @responseText

And I call it like this:

CREATE TRIGGER dbo.trgAfterUpdateORDR ON dbo.ordr
declare @id int
declare @doc_status char
SELECT @id = DocEntry from inserted i
SELECT @doc_status = DocStatus from inserted i
IF @doc_status = 'O'
EXEC alert_open_ordr @id

Works fine except if there is some connection error to the API endpoint (e.g. if server is offline). If the HTTP server is offline, then the SQL transaction itself fails (the one which triggered trgAfterUpdateORDR). How to avoid this situation?


In general, non-transactional things (particularly those with human-visible side effects) should not be part of transactions. Sending emails, calling web services, etc. cannot be rolled back so you don't want them to be called directly from a trigger. For all your trigger knows, the transaction that did the insert might still be rolled back and your alert would reference a transaction that doesn't exist. And it might be retried in which case your human might get flooded with alerts as something tries repeatedly to do the update.

Generally, you'd want to have some sort of queue. Your trigger puts a message in the queue when the document status is O. Some other process picks up the data from this queue and sends the alert. Since they are separate processes, the alerting process will only send alerts once the underlying change commits successfully-- if the transaction setting doc status rolls back, the insert into the queue will be rolled back. There are plenty of ways to implement a queue. The simplest would be a simple table

create table open_order_alerts (
  alertID integer primary key,
  DocEntry integer,
  alertStatus varchar(1),
  enteredQueueTimestamp timestamp,
  exitedQueueTimestamp timestamp

The trigger inserts a row with an open status. An SQL Agent job runs every minute or two, looks for all the open alerts, and loops through them calling the web service. And that notification process either updates the exit timestamp or marks the alert as an error. Most likely, you'd want to implement some sort of retry process (i.e. retry x times before marking the alert as failed permanently).

There are more sophisticated ways to do queuing in the database-- Service Broker for example-- but a queue table is reasonable in most scenarios.

If you really, really want the alert to happen as part of the trigger, you can modify the procedure to have an exception handler that catches any errors coming from the web service, logs them to an error table, and returns. That's generally a bad idea since it means that the caller thinks that the alert has been sent when it hasn't been and it relies on some process/ human monitoring the error log to figure out how often alerts are failing and whether that is a problem rather than simply automatically sending the alerts when the system is back up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.