The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction

Question

From the below code,we are getting exception at raiseerror - The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

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

Because you have SET XACT_ABORT ON' when you do yourRAISERROR()you're setting theXACT_STATE` to -1 which means that you can't do any more committable work to the database, you can only roll back your transaction.

An example using temp procs and one of your triggers above:

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

You have a few options, I believe:

1. Set XACT_ABORT to OFF for this proc. That should handle the XACT_STATE issue for this particular scenario, and your ROLLBACK should handle your trigger issue.
2. Move your ENABLE/DISABLE triggers to your parent proc and handle them outside of the transaction entirely. They don't need to be dependent on your other actions in this child proc; you always disable/enable them anyway.