sql-server - 仅当发生未处理的错误时回滚 sql 事务

Question

我正在创建一个数据库更新机制，该机制在事务中运行多个 SQL 脚本，如果所有脚本都成功，则提交更新。

我面临的问题是，在每个脚本中都可能存在正在处理的错误，但实际上并不被视为错误。

示例:

脚本必须创建一个表并向该表插入一行，但该表已存在。没关系，脚本应该继续并尝试插入该行(如果该行存在) - 也没关系，它应该没有错误地完成并移至下一个脚本。另一方面 - 如果出现意外错误(例如表存在，但列数较少，因此插入失败)，它应该抛出错误并回滚事务。

由于 SQL Server 在每次出现错误时都会回滚我的事务(无论是否已处理)，因此我无法正确执行更新。

示例脚本如下所示:

USE [Database]

BEGIN TRY

-- CREATING TABLE
CREATE TABLE [dbo].[Users](
    [UserId] [int] UNIQUE NOT NULL,
    [UserFullName] [nvarchar](100) NOT NULL,
    [UserName] [nvarchar](100) NOT NULL,
    [UserShortName] [nvarchar](50) NOT NULL,
    [UserLogin] [varchar](50) NOT NULL
)

PRINT 'Created table'
END TRY
BEGIN CATCH
    IF(ERROR_NUMBER() = 2714)
    BEGIN
        PRINT 'Table exists, proceeding to insert'
    END
    ELSE
        THROW
END CATCH

-- INSERTING USER
BEGIN TRY
INSERT INTO [dbo].[Users](
    [UserId]
    ,[UserFullName]
    ,[UserName]
    ,[UserShortName]
    ,[UserLogin]
) VALUES (
    1,'System Administrator','Admin','SA',  'SA'
)
PRINT 'Inserted user'
END TRY
BEGIN CATCH
    IF(ERROR_NUMBER() = 2627)
    BEGIN
        PRINT 'User exists - nothing to insert'
    END
    ELSE
        THROW
END CATCH
GO
PRINT '-- FINISHED add_users_table.sql'

(该脚本只是一个示例，因此可能不是 100% 正确)

如果表或行存在，事务应该继续，但如果抛出不同的错误，则应该回滚。

有办法做到这一点吗？

谢谢!

编辑:

我忘记了机制的一个关键部分。我从 C# 运行这些脚本(它更像是伪代码而不是实际代码，我也知道 SqlCommand 中的 GO 命令不起作用，但它与问题无关):

SqlTransaction transaction = 
connection.BeginTransaction(IsolationLevel.ReadUncommited);

SqlCommand command = new SqlCommand("");
command.Connection = connection;

foreach(string script in scripts)
{
    command.CommandText = script;
    try
    {
        command.ExecuteNonQuery();
    }
    catch()
    {
        transaction.Rollback();
    }
}
transaction.Commit();

Answer 1

对我来说，在运行插入之前检查对象是否存在或用户是否存在似乎更简单，例如:

dbfiddle.uk demo - if exists

if not exists (select 1 from sysobjects where name=N'Users' and xtype='U')
begin;
CREATE TABLE [dbo].[Users](
    [UserId] [int] UNIQUE NOT NULL,
    [UserFullName] [nvarchar](100) NOT NULL,
    [UserName] [nvarchar](100) NOT NULL,
    [UserShortName] [nvarchar](50) NOT NULL,
    [UserLogin] [varchar](50) NOT NULL
);
PRINT 'Created table'
end;

---

if not exists (select 1 from dbo.users where userlogin = 'SA')
begin;
INSERT INTO [dbo].[Users](
    [UserId]
    ,[UserFullName]
    ,[UserName]
    ,[UserShortName]
    ,[UserLogin]
) VALUES (
    1,'System Administrator','Admin','SA',  'SA'
)
PRINT 'Inserted user'
end;