SQL Server XACT_ABORT с исключением

У меня есть большая хранимая процедура, которая использует несколько блоков TRY/CATCH, чтобы отлавливать и регистрировать отдельные ошибки. Я также обернул транзакцию вокруг всего содержимого процедуры, чтобы иметь возможность откатить всю вещь в случае ошибки, возникшей где-то по пути (чтобы предотвратить много грязной очистки); XACT_ABORT был включен, поскольку в противном случае он не откатил бы всю транзакцию.

Ключевой компонент:
В моей базе данных есть таблица, в которую вставляется запись при каждом запуске этой процедуры с результатами операций и подробностями о том, что пошло не так.

Происходит забавная вещь - на самом деле, когда я наконец выяснил, что было не так, это было довольно очевидно... оператор вставки в мою таблицу журналов также откатывается, следовательно, если я не запускаю это из SSMS, Я не смогу увидеть, что это даже было выполнено, так как откат удаляет все трансы активности.

Вопрос:
Можно ли было бы откатить всю транзакцию за исключением этого единственного оператора вставки? Я все еще хотел бы сохранить сообщение об ошибке, которое я компилирую во время выполнения хранимой процедуры.

Спасибо!

~ Eli

Обновление 6/28
Вот пример кода того, на что я смотрю. Ключевое различие между этим и примерами, представленными @Alex и @gameiswar, состоит в том, что в моем случае блоки try/catch все вложены в одну транзакцию. Цель этого состоит в том, чтобы иметь несколько уловов (для нескольких таблиц), хотя мы бы откатили весь беспорядок, даже если последнее обновление не удалось.

SET XACT_ABORT ON;  
BEGIN TRANSACTION  
    DECLARE @message AS VARCHAR(MAX) = '';  

    -- TABLE 1
    BEGIN TRY
        UPDATE TABLE xx 
        SET yy = zz
    END TRY
    BEGIN CATCH
        SET @message = 'TABLE 1 '+ ERROR_MESSAGE();

        INSERT INTO LOGTABLE
        SELECT 
            GETDATE(),
            @message
        RETURN;
    END CATCH

    -- TABLE 2
    BEGIN TRY
        UPDATE TABLE sss 
        SET tt = xyz
    END TRY
    BEGIN CATCH
        SET @message = 'TABLE 2 '+ ERROR_MESSAGE();

        INSERT INTO LOGTABLE
        SELECT 
            GETDATE(),
            @message
        RETURN;
    END CATCH
COMMIT TRANSACTION
Источник

3 ответа

Решение

Хорошо... Я смог решить эту проблему, используя комбинацию замечательных предложений, выдвинутых Алексом и GameisWar, с добавлением оператора потока управления T-SQL GOTO.

Основная идея заключалась в том, чтобы сохранить сообщение об ошибке в переменной, которая переживет откат, а затем Catch отправит вас на метку FAILURE, которая будет выполнять следующие действия:

  • Откат транзакции
  • Вставьте запись в таблицу журнала, используя данные из вышеупомянутой переменной
  • Выход из хранимой процедуры

Я также использую второй оператор GOTO, чтобы убедиться, что при успешном запуске будет пропущен раздел FAILURE и зафиксирована транзакция.

Ниже приведен фрагмент кода того, как выглядел тестовый SQL. Это сработало как прелесть, и я уже реализовал это и протестировал (успешно) в нашей производственной среде.

Я действительно ценю всю помощь и вклад!

SET XACT_ABORT ON               
DECLARE @MESSAGE VARCHAR(MAX) = '';

BEGIN TRANSACTION 
    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST');     -- WORKS FINE
    END TRY 
    BEGIN CATCH     
        SET @MESSAGE = 'ERROR - SECTION 1: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST2');        --WORKS FINE
        INSERT INTO TEST_TABLE VALUES ('ANOTHER TEST'); -- ERRORS OUT, DATA WOULD BE TRUNCATED
    END TRY 
    BEGIN CATCH 
        SET @MESSAGE = 'ERROR - SECTION 2: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

GOTO SUCCESS;

FAILURE:        
    ROLLBACK
    INSERT INTO LOGG SELECT @MESSAGE
    RETURN; 

SUCCESS:
COMMIT TRANSACTION
Источник

Вы можете попробовать что-то вроде ниже, что гарантирует, что вы регистрируете операцию. Это использует тот факт, что переменные таблицы не откатываются.

Код Psuedo только для того, чтобы дать вам представление:

create table test1
(
id int primary key
)

create table logg
(
errmsg varchar(max)
)



declare @errmsg varchar(max)

set xact_abort on
begin try
begin tran
insert into test1
select 1

insert into test1
select 1

commit
end try

begin catch
set @errmsg=ERROR_MESSAGE()
select @errmsg as "in block"
if @@trancount>0
rollback tran

end catch
set xact_abort off


select @errmsg as "after block";

insert into logg
select @errmsg


select * from logg
Источник

Я не знаю деталей, но ИМХО общая логика может быть такой.

--set XACT_ABORT ON --not include it
declare @result varchar(max) --collect details in case you need it
begin transaction
begin try
--your logic here
--if something wrong RAISERROR(...@result)
--everything OK
commit
end try
begin catch
--collect error_message() and other into @result
rollback
end catch
insert log(result) values (@result)
Источник
Другие вопросы по тегам