Вызов API Freshdesk из триггера SQL Server

Question

Мой клиент хочет, чтобы триггер вставки в его таблице заказов (из Sage) создавал билет Freshdesk с помощью API.

Как часть моей разработки, я построил хранимую процедуру, которая отлично справляется со своей задачей, когда ей предоставляется номер заказа. Однако трансплантация того же кода в триггер возвращается без ошибок, но в системе Freshdesk ничего не появляется, когда работает тот же код в хранимой процедуре.

Я ожидаю комментариев о том, почему вызов API в триггере может быть плохой идеей, но вызов Freshdesk очень быстрый (<1 секунда от хранимой процедуры).

То, что я хотел бы знать, - это архитектурно запрещено SQL Server по какой-то причине? Если это разрешено, где я могу найти ошибку, которая выдается.

Edit2: хорошо, вот весь триггер.. предыдущая версия просто имела вызовы OA.

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

Это код триггера.

Хранимая процедура с тем же кодом (но с номером заказа в качестве параметра) работает правильно, выполняет вызов API и ведет журнал. Комментирование записи в конце триггера убрало ошибку Sage, но вызов API все еще не поступил.

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

Вы закомментировали 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 Таблица. It is likely that this SELECT doesn't find a row with the given @ORDNUM and variables remain NULL,

---

Примечание. Triggers in SQL Server are fired once per the statement, not once per row. Your trigger should work correctly even if inserted Таблица имеет несколько строк. Прямо сейчас ваш триггер выберет только один ORDNUMBER даже если в таблицу было вставлено несколько строк. Скорее всего, это не то, что вы хотите.

---

Как отладить триггер?

Один простой и очень простой способ - создать таблицу для регистрации и добавить множество INSERT операторы, которые будут записывать значения всех переменных в эту таблицу. Затем вы можете прочитать журналы и посмотреть, что происходит.