当任何insert语句在存储过程中失败时，回滚整个txn

I've a stored procedure where there are two insert statement for two different tables.

我有一个存储过程,其中有两个不同的表的insert语句。

create procedure p_multiple_insert
(
// variable declaration
)
declare @temp int = 0;
begin try
    begin 
    // insert statement for TABLE1
        set @temp = 1;
    if(@temp = 1)
        begin
            // insert statement for TABLE2
        end
    end
end try
begin catch
// insert into error table error_number() & error_message()
end catch

If the error occurs with first insert statement block, @temp = 0. So second insert block does not execute and data is also not inserted in the TABLE1.

如果第一个插入语句块发生错误,则@temp = 0.因此第二个插入块不会执行,并且数据也不会插入TABLE1中。

But if the error occur while inserting into TABLE2, how can I rollback my whole transaction. I mean roll back first insert statment too.

但是如果在插入TABLE2时发生错误,我该如何回滚整个事务。我的意思是回滚第一个插入法则。

1 个解决方案

#1

my changes are in UPPER CASE:

我的更改是大写的:

create procedure p_multiple_insert
(
-- variable declaration
)
declare @temp int = 0;

BEGIN TRANSACTION

begin try
    -- insert statement for TABLE1
        set @temp = 1;
    if(@temp = 1)
        begin
            -- insert statement for TABLE2
        end
    end

    COMMIT TRANSACTION

end try
begin catch

    ROLLBACK TRANSACTION

    -- insert into error table error_number() & error_message()
end catch

BEGIN TRANSACTION at the beginning

一开始就开始交易

COMMIT at the end of try block

在try块结束时执行COMMIT

ROLLBACK everything in catch-block

ROLLBACK catch-block中的所有内容

My "Standard-Catch-Block" always looks like this:

我的“Standard-Catch-Block”总是如下所示:

    DECLARE @errmsg NVARCHAR(MAX)
    SELECT  @errmsg = 'Error executing dbo.StoredProcName: ' 
                         + COALESCE(ERROR_MESSAGE(),'No Message from SQL Server')
    RAISERROR(@errmsg,16,1)
    ROLLBACK TRANSACTION

#1