0

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;
10
  • Ugh. Have you tried SET XACT_ABORT OFF in the trigger (implicitly it is on) instead of that horrible workaround? Jul 6, 2023 at 16:41
  • @MartinSmith - Thanks for the reply. I commented out the COMMIT TRANS and BEGIN TRANS and added SET XACT_ABORT OFF at the top, however, when I reload table, no records are saved.
    – Ross Bush
    Jul 6, 2023 at 16:46
  • Is this basically what you are trying to do? dbfiddle.uk/svf1vwQR Jul 6, 2023 at 16:58
  • @MartinSmith - Thanks, I tried something like this yesterday. When I replace EXECUTE (@Command) with SET @X = 1 / 0, then all the data comes across and I get 120 divide by zero errors
    – Ross Bush
    Jul 6, 2023 at 17:01
  • 1
    Right, if I try the command EXECUTE ('CREATE PROC P1 AS SELECT 1') WITH RESULT SETS NONE - which fails on the second attempt as the proc already exists - then SET XACT_ABORT OFF doesn't save the day. The transaction is still doomed Jul 6, 2023 at 17:21

2 Answers 2

2

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.

1
  • Thanks for the comment. I have been working on plan B that is similar. Table at replication source containing all ongoing ddl changes get created and replicated to the target. Using the ID against a non-replicated table at the target maintaining already ran commands (remains during new snapshot or full load), only new commands get executed. My problem is attempting to solution this with a trigger on the second non-replicated audit table. I will look into the above approach or have a job run periodically marking commands in the non-replicated table as IsApplied or something like that.
    – Ross Bush
    Jul 6, 2023 at 17:22
0

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

Uncommittable Transactions

1
  • This is very vague. What answer did you find, and how did it address your problem? If you can, edit your answer to clarify please.
    – Paul White
    Aug 4, 2023 at 10:10

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.