Geek asks and answers

The current transaction cannot be committed and cannot support operations that are written to the log file. Transaction rollback

From the code below, we get an exception in raiseerror. The current transaction cannot be committed and cannot support operations that are written to the log file. Cancel 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;
+5
source share
2 answers

Since you have SET XACT_ABORT ON' when you do yourRAISERROR () you're setting theXACT_STATE` to -1, which means that you cannot do any transactional work in the database, you can only cancel the transaction.

An example of 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 several options, I believe:

  • XAC XACT_ABORT OFF. XACT_STATE ROLLBACK .
  • ENABLE/DISABLE proc . proc; / .
+6

: . Windows .

0

More articles:


All Articles