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.
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;