Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail

Question

I am not a fan of triggers and dynamic sql, however, what I am working with requires both.

CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN   
    SET NOCOUNT ON;
    DECLARE @DatabaseName NVARCHAR(100)
    DECLARE @FormItemID INT

    DECLARE db_cursor CURSOR FOR
    SELECT SourceID,FormItemID from Inserted

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

        BEGIN TRY    
            EXEC spAddEditFormItemView @FormItemID, @DatabaseName WITH RESULT SETS NONE
        END TRY    
        BEGIN CATCH 
            INSERT INTO AdminErrorLog(SourceID, ErrorNumber, ErrorState, ErrorSeverity, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime) 
            SELECT @DatabaseName, ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
        END CATCH     

        FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID
    END

    CLOSE db_cursor  
    DEALLOCATE db_cursor
END 

Here is what is happening.

1. An AWS DMS Replication Task is replicating data in batches of 10,000. The implementation of the replication is a black box but there appears to be nested transactions on the connections.
2. EXEC spAddEditFormItemView calls a stored procedure that exec's dynamic sql and errors out on one record.
3. The CATCH block is never executed
4. The error never makes it to AdminErrorLog.
5. The AWS Batch errors our and is never committed and the whole task fails.
6. In extended events the following sql error is caught. This is the error that failed the task. I read about conditions where commands can neither be in a commit or rollback state but truly did not fully understand that concept.

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

severity: 16

Can anyone explain why the catch block is not being caught and why the client application is getting, what my guess is, a sql exception and rolling everything back. My guess is that dynamic sql exceptions are being handled differently and the implicit trigger transaction is rolling back instead of catching the error. Also, does anyone know of a way to avoid the exception from being propagated?

I bet the safest solution would be modify the trigger to cram the sql commands into a table and then have a sql agent job look for commands to run every minute or so.

EDIT - Adding Procedure to recreate: In SSSM :

EXEC [spAddEditFormItemView] 30032,'VP_BENCHMARKING_V05'

Commands completed successfully.

Completion time: 2023-11-27T16:11:18.1762097-05:00

In the trigger it forces a rollback with a horrible error message.

ALTER PROCEDURE [dbo].[spAddEditFormItemView] ( 
    @FormItemID int,
    @Schema nvarchar(100)
)
AS
    DECLARE @SQL NVARCHAR(MAX) = 'e2e2e2e2e'
    BEGIN TRY   
        EXEC (@SQLCommand)      
    END TRY
    BEGIN CATCH
        DECLARE @X INT
    END CATCH

EDIT: I can now duplicate in SSMS.

Answer 1

The trigger runs as part of the transaction. So whether the catch block runs or not, the transaction is doomed and the can never be committed to the AdminErrorLog table.

So it doesn't really matter if the CATCH block executes. But it doesn't because errors in trigger scope generally abort the batch. See Erland Sommarskog's classic article on TSQL error handling here: https://www.sommarskog.se/error-handling-I.html#triggercontext