Variable Value Set in TRY CATCH is Discarded When Trigger is Coded a Certain Way

Question

I know this goes against all things logical, however, I am attempting to push this through anyway :(

There is a scenario in which records are written to a table with the trigger below attached. Without the kludge described below, no records get written. This is not batch related.

Adding the COMMIT TRAN and BEGIN TRAN in the manner below came from the last answer to this DA Question

I have 120 records come through and get written to the table. Out of 120, 90 throw exceptions when EXECUTE(@Command) is called, however, the value of @ErrorMessage is 'XXX' for all 120 records, even when there is an exception. I would think that I would see the 90 error messages, but it is like the @ErrorMessage being set in the TRY CATCH is discarded. Can anyone explain why this would happen and if there is a workaround?

ALTER TRIGGER [dbo].[LoadAWSObjects]
ON [dbo].[tblAWSObjectsControl]
AFTER INSERT   
AS  
BEGIN
    COMMIT TRANSACTION -- This is a kludge to have the trigger's internal transaction committed regardless of an Exception in the TRY
                       --See Attached DA Question below 

    DECLARE @ErrorMessage NVARCHAR(4000) = 'XXX'

    SELECT @Command = Command from inserted
    
    BEGIN TRY
    
       EXECUTE (@Command) WITH RESULT SETS NONE

    END TRY
    BEGIN CATCH  

       --This should hit 90 times 
       SET @ErrorMessage = ERROR_MESSAGE()  

    END CATCH

    BEGIN TRANSACTION -- This is a kludge to have the triggers internal transaction committed regardless of an Exception in the TRY
                      --See Attached DA Question below 
    
    --There are 120 records in the log table with an ErrorMessage of 'XXX'  
    INSERT INTO tblAWSObjectsErrorLog(ErrorMessage)
    SELECT @ErrorMessage
END;

Answer 1

Triggers were implemented assuming that they will be part of the original transaction. Attempting to execute arbitrary SQL that somehow isn't part of that transaction is difficult as you're attempting something in the opposite way it's supposed to work. Stop banging your head into a wall....

A clean, well-documented and fully supported approach is to use service broker to asynchronously queue up the statements to execute.

The trigger logic is simplified:

1. begin dialog conversation on queue.
2. send message on queue containing SQL to execute.
3. wait for reply (or not if you want "fire and forget")
4. end dialog conversation.

The queue will have an activation sp that executes the sql and logs the results.

Errors executing the SQL are 100% disconnected from the original transaction.

You can also multi-thread the activation sp (MAX_QUEUE_READERS = ), hence parallelizing your workload.

Answer 2

I found the answer around 3/4 of the way down this documentation.

Uncommittable Transactions