SQL Server 2012 - триггер не может зафиксировать данные локально, несмотря на попытку извлечения для sp_testlinkedserver

Question

Если я внесу изменения в MyTable на MyDatabaseЯ хочу удалить содержимое TestTable на TestDatabase на MYLINKEDSERVER и вставьте все заново. Для этого я использую триггер на MyTable, Я знаю, что это неэффективно, но количество записей меньше 10.

Если связанный сервер не работает, я хочу, чтобы он по-прежнему фиксировал изменения локально MyTable и просто отправьте уведомление по электронной почте о том, что связанный сервер не может быть обновлен. Мне не удается зафиксировать локальную транзакцию, когда связанный сервер недоступен... Я пытался возиться с XACT_ABORT но я просто получаю разные ошибки.

Что я делаю неправильно?

CREATE TRIGGER trig_updatelinkedserver ON MyDatabase.dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @linked_server SYSNAME = 'MYLINKEDSERVER'
, @tablename SYSNAME --name of the table calling the trigger so we can send error details in alert eamil

SELECT @tablename = OBJECT_NAME(parent_object_id) 
FROM sys.objects 
WHERE sys.objects.name = OBJECT_NAME(@@PROCID)

BEGIN TRY
    --If linkedserver fails to connect, we do not want the remaining code in the block to run
    --but we do want the original transaction that triggered this to complete.
    EXEC sp_testlinkedserver  @servername = @linked_server

    DELETE FROM MYLINKEDSERVER.TestDatabase.dbo.TestTable

    INSERT INTO MYLINKEDSERVER.TestDatabase.dbo.TestTable
    SELECT *
    FROM MyDatabase.dbo.MyTable
END TRY

BEGIN CATCH
    DECLARE @subj VARCHAR(1000) = 'TRIGGER FAILURE: ' + @tablename + ': Could not locate linkedserver ' + @linked_server

    EXEC msdb.dbo.sp_send_dbmail 
    @recipients = '[e-mail]'
    , @subject = @subj
    , @body = ''
    , @body_format = 'HTML'
    , @profile_name = 'MyEmailProfile'
END CATCH

END

Answer 1

Обработка ошибок в tsql сложна и противоречива. Что ты делаешь не так? Делать предположения. Вот что говорит Эрланд в своей длительной дискуссии на эту тему:

Что важно понять о триггерах, так это то, что они являются частью команды, которая сработала, и в триггере вы всегда находитесь в транзакции, даже если вы не использовали BEGIN TRANSACTION. Иногда я вижу, как люди на форумах по SQL Server спрашивают, могут ли они написать триггер, который не откатывает команду, которая сработала, если триггер не срабатывает. Ответ в том, что вы не можете сделать это надежно, поэтому лучше даже не пытаться. Если у вас есть такой тип требований, вам, вероятно, вообще не следует использовать триггер, а используйте другое решение.

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