Freshdesk API call from SQL Server trigger

Question

My client wants to run the insert trigger in their Order table (from Sage) in order to create a Freshdesk ticket using the API.

As part of my development, I created a stored procedure that does an excellent job of providing an order number. However, transplanting the same code into the trigger returns without errors, but nothing appears in the Freshdesk system when the same code works in the stored procedure.

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

What I would like to know is for some reason this is prohibited by SQL Server architecture? If allowed, where can I look for the error you have selected.

Edit2: OK, here is the whole trigger. In the simplest version, there were only 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 

This startup code.

A stored procedure that has the same code (but takes the order number as a parameter) works correctly and calls the API call and logs. Commenting on registration at the end of the trigger, the Sage error is gone, but the API call has not yet arrived.

Answer 1

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

---

One note is the 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 on FROM inserted and try to read the values ​​from the table directly.

When the startup code is run, the transaction has not yet completed, so most likely you should read the values ​​from the inserted table. This SELECT will probably not find the row with the given @ORDNUM , and the variables remain NULL .

---

Side note. Triggers in SQL Server run once per statement, not once per line. Your trigger should work correctly, even if the inserted table has multiple rows. Right now, your trigger will select only one ORDNUMBER , even if multiple rows have been inserted into the table. Most likely, this is not what you want.

---

How to debug a trigger?

One simple and very simple way is to create a table for logging and add many INSERT that will write the values ​​of all variables to this table. Then you can read the magazines and see what happens.