Calling Freshdesk API from a SQL Server trigger

Question

My client wants an insert trigger on his Order table (from Sage) to create a Freshdesk ticket using the API.

As part of my development, I built a stored procedure that does the job fine when provided with an order number. However, transplanting the same code into a trigger returns without error, but nothing appears in the Freshdesk system, when the same code in a stored procedure works.

I expect comments about why an API call in a trigger might be a bad idea, but the Freshdesk call is very quick (<1 second from the stored procedure).

What I'd like to know is -- Is this is architecturally forbidden by SQL Server for some reason? If it's allowed, where might I look for the error that's being thrown.

Edit2: OK, here's the whole trigger .. pervious version just had OA calls.

ALTER TRIGGER [dbo].[CreateFreshdeskTicketFromOrder] 
   ON  [dbo].[OEORDH] 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  -- Get the original order number, and use that in the main lookup query
  DECLARE @ORDNUM VARCHAR(22)
  SELECT @ORDNUM = ORDNUMBER FROM inserted

  -- Variables for fields going to the API
  DECLARE @EMAIL VARCHAR(60), @SHPCONTACT VARCHAR(60), @ORDNUMBER VARCHAR(22)
  DECLARE @LOCATION VARCHAR(6), @EXPDATE INT
  DECLARE @SHPPHONEC VARCHAR(30), @SHPNAME VARCHAR(60), @DESCR VARCHAR(60)
  DECLARE @CODEEMPL VARCHAR(15)

  -- Collect field values that were just inserted
  SELECT
    @EMAIL = rtrim(OEORDH1.SHPEMAILC), @SHPCONTACT = rtrim(SHPCONTACT),
    @ORDNUMBER = rtrim(ORDNUMBER), @LOCATION = LOCATION, @EXPDATE = EXPDATE,
    @SHPPHONEC = rtrim(OEORDH1.SHPPHONEC), @SHPNAME = SHPNAME,
    @DESCR = rtrim([DESC]), @CODEEMPL = rtrim(ARSAP.CODEEMPL)
  -- FROM inserted
  FROM dbo.OEORDH
  JOIN dbo.OEORDH1 on dbo.OEORDH.ORDUNIQ   = dbo.OEORDH1.ORDUNIQ
  JOIN dbo.ARSAP   on dbo.OEORDH.SALESPER1 = dbo.ARSAP.CODESLSP
  WHERE ORDNUMBER = @ORDNUM

  -- Variables from database to the API call
  DECLARE @EXPDATE_OUT VARCHAR(10)
  SET @EXPDATE_OUT =
    substring ( cast ( @EXPDATE as varchar(8) ), 1, 4 ) + '-' +
    substring ( cast ( @EXPDATE as varchar(8) ), 5, 2 ) + '-' +
    substring ( cast ( @EXPDATE as varchar(8) ), 7, 2 );

  DECLARE @STATUS_OUT VARCHAR(2)
  IF @LOCATION = '1A'
    SET @STATUS_OUT = '23';
  ELSE
    IF @LOCATION = '1'
      SET @STATUS_OUT = '40';
    ELSE
      SET @STATUS_OUT = '2';

  -- Variables for building the API call
  DECLARE @Object INT
  DECLARE @Url VARCHAR(80)

  DECLARE @Body1 VARCHAR(1000) =
  '{ ' +
    '"email": "'+ @EMAIL +'", ' +
    '"custom_fields": { "order_number": "'+ @ORDNUMBER +'", "scheduled_date": "'+ @EXPDATE_OUT + '", ' +
    '"delivered_to": "'+ @SHPCONTACT + '", ' + '"consignee_phone_number": "'+ @SHPPHONEC +'" }, ' +
    '"status": '+ @STATUS_OUT + ', ' +
    '"priority": 1, "subject": "'+ rtrim(@ORDNUMBER) + ' - ' + rtrim(@SHPNAME) + ' (' + @DESCR + ')", ' +
    '"responder_id": ' + @CODEEMPL +
  ' }'

  DECLARE @ResponseText VARCHAR(1000), @return_status INT

  SET @Url = 'https://client.freshdesk.com/api/v2/tickets';

  -- Do REST call to API / All return statuses commented out except for last
  Exec @return_status = sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
  -- Select 'Create return', @return_status

  Exec @return_status = sp_OAMethod @Object, 'Open', NULL, 'POST', @Url, false
  -- Select 'Open return', @return_status

  Exec @return_status = sp_OAMethod @Object, 'setRequestHeader', NULL,
    'Content-Type', 'application/json'
  -- Select 'Set Request Header1 return', @return_status
  Exec @return_status = sp_OAMethod @Object, 'setRequestHeader', NULL,
    'Authorization', 'Basic ABC123=='
  -- Select 'Set Request Header2 return', @return_status

  Exec @return_status = sp_OAMethod @Object, 'Send', NULL, @Body1
  -- Select 'Send1 return', @return_status

  Exec sp_OAMethod @Object, 'ResponseText', @ResponseText OUT
  -- Select 'Response', @ResponseText

  Exec sp_OADestroy @Object

  -- Add the conversation to the TriggerLog

  IF @ResponseText IS NULL
    SET @ResponseText = '(Null)';

  INSERT INTO dbo.TriggerLog (tl_source, tl_input, tl_output) VALUES
    ( 'FreshdeskInsertTrigger', @Body1, @ResponseText )
END

That's the trigger code.

A stored procedure that has the same code (but takes an order number as a parameter) works correctly and does the API call and does the logging. Commenting out the logging at the end of the trigger made the error from Sage go away, but the API call still didn't arrive.

Answer 1

What happens if you simply call your working stored procedure from the trigger (via EXEC) instead of including the procedure's code in the trigger?

---

One thing to look closely at is this place in the code:

  -- Collect field values that were just inserted
  SELECT
    @EMAIL = rtrim(OEORDH1.SHPEMAILC), @SHPCONTACT = rtrim(SHPCONTACT),
    @ORDNUMBER = rtrim(ORDNUMBER), @LOCATION = LOCATION, @EXPDATE = EXPDATE,
    @SHPPHONEC = rtrim(OEORDH1.SHPPHONEC), @SHPNAME = SHPNAME,
    @DESCR = rtrim([DESC]), @CODEEMPL = rtrim(ARSAP.CODEEMPL)
  -- FROM inserted
  FROM dbo.OEORDH
  JOIN dbo.OEORDH1 on dbo.OEORDH.ORDUNIQ   = dbo.OEORDH1.ORDUNIQ
  JOIN dbo.ARSAP   on dbo.OEORDH.SALESPER1 = dbo.ARSAP.CODESLSP
  WHERE ORDNUMBER = @ORDNUM

You commented out FROM inserted and try to read values from the table directly.

When the trigger code runs the transaction is not committed yet, so most likely you should read the values from the inserted table. It is likely that this SELECT doesn't find a row with the given @ORDNUM and variables remain NULL.

---

Side note. Triggers in SQL Server are fired once per the statement, not once per row. Your trigger should work correctly even if inserted table has several rows. Right now your trigger would pick only one ORDNUMBER even if several rows were inserted into the table. Most likely this is not what you want.

---

How to debug the trigger?

One simple and very straight-forward way is to create a table for logging and add plenty of INSERT statements that would log values of all variables into that table. Then you can read through the logs and see what was going on.