4

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.

enter image description here

2
  • Not enough info here. What is the procedure actually doing that it needs dynamic SQL? What is the actual error that is causing the problems, and can it be worked around? If not why can it not be caught, is it a batch-aborting error even with a CATCH? Why is there a CATCH block that does nothing? Nov 28, 2023 at 11:40
  • @Charlieface - There actual stored procedure was very verbose. I condensed it down to the core issue. There was an error being thrown in an EXEC(@SQL) in the stored procedure and that error was surpassing all CATCH statements in both the SP and the trigger. That is the behavior when a non-user exception occurs within a trigger's transaction scope (see document inn answer). I have a work around in place. Since I rarely work with this kind of hobbling together of needs, this is the first time I ran into this particular issue.
    – Ross Bush
    Nov 28, 2023 at 13:31

1 Answer 1

5

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

2
  • 1
    The best alternative I fathom is to use the trigger to queue the generated sql commands somewhere and then run them independently. It is not critical if the statement fails, it just needs to be logged when it does.
    – Ross Bush
    Nov 27, 2023 at 23:01
  • 2
    I would just insert data in the trigger (FormItemID, DatabaseName, InsertTime, Status, Message) into a table, and have an agent job run spAddEditFormItemView and update the status (or delete the rows). Nov 28, 2023 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.