Делаем вставку без отката

Вот странный вопрос: есть ли способ сделать вставку без отката в таблицу?

Вот сценарий: у нас есть триггер, который делает вещи.

Иногда этот триггер будет вызывать RAISERRROR(), И внешняя транзакция откатывается.

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

Источник

3 ответа

Решение

улов банана

Вы можете ловить исключения с try-catch и передать / выбросить их во внешнюю область видимости, где должны существовать такие же вещи. Это, кстати, позволяет собирать call-stack, Это решение может быть реализовано, если вы работаете только через сохраненные программы. Каждый процесс должен иметь такой шаблон:

begin try
end try
begin catch
   if @@trancount > 0
     rollback

   insert into <log> (...)
   values (...)

   throw
end catch

Таким образом, самая верхняя процедура успешно вставит строку в таблицу журнала.

Минусы:

  • с глубокой иерархией - слишком много "фиктивных" вставок для отката
  • все еще можно ничего не регистрировать, если:
    • тяжесть слишком высока
    • ИП не соответствует этому образцу
    • есть внешнее / клиентское управление транзакциями

Плюсы:

  • Не идеально, но выполнимо и не слишком сложно для реализации.

Обратная связь:

  • Я использовал этот подход на производственном сервере небольшого проекта, собирал стек вызовов и так далее. Это было бэк-офисное программное обеспечение с ~50-100 онлайн-пользователями, с некоторым количеством работающих роботов, далеко не "высоконагруженным". Работало нормально, помогло исправить многие проблемы. Накладные расходы никого не беспокоили.

неуловимый бандит

Вы можете создать CLR-сборку, которая будет вставлена ​​в таблицу журнала и ничего более... Но! Можно указать отдельное соединение, чтобы эта сборка работала с БД через это отдельное соединение. Что означает - в отдельной области. Таким образом, метод этой сборки вызывается из транзакции, однако выполняется независимо от этой области транзакции.

Итак, вместо:

using(SqlConnection connection = new SqlConnection("context connection=true"))

просто укажите обычную строку подключения. После вызова - выбросить исключение с измененным ERROR_STATUS чтобы избежать дополнительной регистрации той же ошибки.

begin try
end try
begin catch
   if @@trancount > 0
     rollback

   if @@ERRROR_STATUS != @done_with_logging
     exec asm.log(...)

   raiserror @err_msg, @severity, @done_with_logging
end catch

Минусы:

  • может привести к проблемам в пуле соединений
  • немного длинный способ получить тривиальную вставку
  • может вызвать дополнительные проблемы, связанные с установлением соединения и самой сборки (+ разрешения, владение и т. д.)

Плюсы:

  • идея немного волнующая
  • может войти в файл вместо БД

Обратная связь:

  • Я не могу вспомнить, использовал ли я этот подход на prod (хотя я вспоминаю это как более недавний опыт... или просто как попытку реализовать это в целях тестирования)

тук, тук, админ

Это простое утверждение (на самом деле просто дополнительная опция WITH LOG) запишет любое сообщение об ошибке в журнал событий SQL SERVER:

RAISERROR(...) WITH LOG

Это не тот способ, которым должен использоваться журнал SQL SERVER, но это самый быстрый способ записать что-то важное (для решения проблемы). Зарегистрированные события можно посмотреть в окнах агента SSMS.

Минусы:

  • могут быть соображения разрешений
  • плохой поиск и нулевые возможности настройки
  • dba будет тебя ненавидеть (если он, конечно, будет проверять логи сервера)

Плюсы:

  • однострочная реализация

Обратная связь:

  • Я сталкивался с таким способом регистрации только в одной компании. Я предполагаю, что некоторое время назад он использовался несколькими разработчиками для обнаружения незначительных ошибок, но позже получил распространение по всему коду и стал "стандартом". Итак, через некоторое время стало почти невозможно найти что-то конкретное в журнале событий... и фактически лишь немногие коллеги имели доступ к журналу сервера Prod. Так что буквально а) вредно б) бесполезно. Так что я избавился от этого из управляемой части системы, как только нашел время для.

летающий голландец

{место для разговоров на основе мнения о построении системы на основе триггеров DML}

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

Если ваш проект представляет собой клиент-серверное приложение без приложения appserver / backend, и все, что вы получили, - это специальный запрос и триггер, то для регистрации не так много данных. Нет стека вызовов (на стороне сервера). И будет трудно определить, как вы (пользователь, приложение) пришли к этому конкретному исключению. Таким образом, регистрация на стороне клиента может быть более полезной в этом случае.

Источник

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

Например

declare @tab table (msg varchar(255))

BEGIN TRY

    BEGIN TRANSACTION

    select 1+2

    INSERT @tab values ('first step complete')

    SElect 1/0

    INSERT @tab values ('2nd step complete')

    COMMIT

END TRY

BEGIN CATCH

    ROLLBACK

    SELECT * FROM @tab
END CATCH
Источник

Пишу это через некоторое время. Вы можете использовать вместо триггера:

      create table t
(
i int,
s varchar(10)
)
go

create table t2
(
i int,
s varchar(10)
)
go

create table tlog
(
i int,
s varchar(10)
)
go

alter trigger tt on t
INSTEAD OF INSERT
AS
BEGIN
    rollback transaction
    raiserror ('something went wrong', 16, 2)
    insert tlog (i,s)
    select i, s
    from inserted
END
go

truncate table t
truncate table t2
truncate table tlog
go

select * from t
select * from t2
select * from tlog
go

begin transaction
insert t2 (i,s) values (1, 'abc')
insert t (i,s) values (1, 'abc')
commit transaction
go

select * from t
select * from t2
select * from tlog

Получил следующий вывод:

является

(Затронуты 0 строк)

является

(Затронуты 0 строк)

является

(Затронуты 0 строк)

(Затронуто 1 строка (строки)) Сообщение 50000, уровень 16, состояние 2, процедура tt, строка 7 что-то пошло не так

(Затронуто 1 строка (строки)) Сообщение 3609, уровень 16, состояние 1, строка 4 Транзакция завершилась срабатыванием триггера. Пакет был прерван. является

(Затронуты 0 строк)

является

(Затронуты 0 строк)

является

1 abc

(Затронута 1 строка (и))

Источник
Другие вопросы по тегам