不能提交当前事务，也不能支持向日志文件写入的操作.回滚事务

Question

从下面的代码中，我们在raiseerror处得到异常-当前事务不能提交，不能支持写日志文件的操作。回滚事务

IF @insertOrUdate = 'D'
BEGIN
    -- DescType depends on CorrectionType and is also a protected sync table, 
    --  it needs to be cleared out before we can remove this type
    IF EXISTS(
        SELECT TOP 1 * 
        FROM [dbo].[DescType]
        WHERE 
            [CorrectionTypeId] = @correctionTypeId
    )
    BEGIN
    PRINT 'raise error'
        RAISERROR('Dependent Desc Role Type Rollups must be removed prior to removing a type that they depend on', 16, 1)
        PRINT 'after raise error'
    END
    -- Delete protected Sync record
    DELETE FROM [dbo].[CorrectionType] WHERE [CorrectionTypeId] = @correctionTypeId;
END;

Answer 1

因为你有SET XACT_ABORT ON' when you do your RAISERROR() you're setting the XACT_STATE '为-1，这意味着你不能再向数据库做任何可提交的工作，你只能回滚你的事务。

一个使用临时进程和上面的一个触发器的例子:

create proc #a 
as
--This is the proxy for the parent proc
begin try
begin tran
    exec #b
commit tran
end try
begin catch
    if @@trancount > 0 rollback
    select error_message();
end catch
go
create proc #b
as
set xact_abort on; 
begin try;
    DISABLE TRIGGER [dbo].[trg_dml_CorrectionType_InsteadOfDelete] ON [dbo].[CorrectionType];
    --Check  state
    select xact_state() one;
    raiserror('Error!', 16,1)
    --This one doesn't run of course
    select xact_state() two
end try
begin catch
    select xact_state() three;
    select error_message() as msgprior;
ENABLE TRIGGER [dbo].[trg_dml_CorrectionType_InsteadOfDelete] ON [dbo].[CorrectionType];
    --This doesn't run either, new error
    select xact_state() four;
    --if @@trancount > 0 rollback transaction;
    declare @error nvarchar(2500)
    select @error = error_message()
    raiserror(@error, 16,1);
end catch
GO
exec #a

你有几个选择，我相信:

1. 设置XACT_ABORT为OFFXACT_STATE问题，以及ROLLBACK应该处理你的触发问题。
2. 移动ENABLE/DISABLE触发器到父进程，并在事务之外处理它们完全。它们不需要依赖于你的其他行为子进程;你总是禁用/启用它们。

Answer 2

在我的例子中，这被证明是环境问题:分布式事务协调器服务已经停止。重新启动这个Windows服务为我们修复了这个问题。