Trigger with EXEC and Only Debug Statements After First Exception Saved

Question

I apologize for the verbosity of this post.

To catch DDL changes after a snapshot or full load (using aws/dms here) has been performed, there is sourceDB.tblAWSObjects which replicates to targetDB.tblAWSObjects.

In the SourceDB , a database DDL Trigger creates records in the tblAWSObjects for ALTER/CREATE/DELETE on triggers, views and functions.

1. There are 10 records in sourceDB.tblAWSObjects.

2. There are 0 records in targetDB.tblAWSObjects

3. Replication starts as DROP/CREATE and target table targetDB.tblAWSObjects is dropped/created.

4. A Database Trigger Fires FOR CREATE_TABLE on targetDB.

   a. This trigger checks if the table is tblAWSObjectsand creates a table trigger.

   b. Trigger contents

     SET @cmd = 
        'CREATE TRIGGER LoadAWSObjectsControl
        ON dbo.tblAWSObjects
        AFTER INSERT   
        AS  
        BEGIN
    DECLARE @ID INT = (SELECT ID FROM inserted)
   
    BEGIN TRY
        INSERT INTO _Test2([Count],[Message])VALUES(0, ''BEFORE tblAWSObject INSERT'')
   
        INSERT INTO [tblAWSObjectsControl]([ID],[Command],[PostTime],[HostName],[LoginName],[DBName],aws_record_datetime,IsApplied)
        SELECT
            i.[ID], i.[Command],i.[PostTime],i.[HostName],i.[LoginName],i.[DBName],GETDATE(), 0 AS IsApplied
        FROM
            Inserted i
            LEFT OUTER JOIN tblAWSObjectsControl c ON i.ID = c.ID
        WHERE
            c.ID IS NULL
   
    END TRY
    BEGIN CATCH
        --@@TRANCOUNT = 2 HERE
        INSERT INTO _Test2([Count],[Message])VALUES(@ID, ''IN tblAWSObject CATCH'')
    END CATCH
   
    INSERT INTO _Test2([Count],[Message])VALUES(@ID, ''AFTER tblAWSObject INSERT'')
   
    END
    '
   
    EXEC (@cmd) WITH RESULT SETS NONE;
   
   

5. There is a non-replicated table named tblAWSObjectsControl with a trigger.

   ALTER TRIGGER [dbo].[LoadAWSObjects]
   ON [dbo].[tblAWSObjectsControl]
   AFTER INSERT   
   AS  
   BEGIN
    DECLARE @Command NVARCHAR(MAX)
   SELECT @Command = Command from inserted
   INSERT INTO _Test2([Count],[Message])VALUES(0, 'BEFORE tblAWSObjectsControl COMMAND')
   BEGIN TRY
   
        EXECUTE (@Command) WITH RESULT SETS NONE -- Everything is fine when this is commented. 
          --When an error occurs nothing is replicated 
          --and only trace messages for the one exception, which resides in record 7 out of 10 records, gets saved to _TestTable and tblAWSObjectsErrorLog.
    END TRY
    BEGIN CATCH
   
        DECLARE @Trig_Error TABLE([ErrorNumber] [int] NULL, [ErrorState] [int] NULL,[ErrorSeverity] [int] NULL, [ErrorProcedure] nvarchar NULL,  [ErrorLine] [int] NULL, [ErrorMessage] nvarchar NULL,[ErrorDateTime] [datetime] NOT NULL);
    INSERT INTO @Trig_Error([ErrorNumber],[ErrorState],[ErrorSeverity],[ErrorProcedure],[ErrorLine],[ErrorMessage],[ErrorDateTime]) 
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_STATE() AS ErrorState,ERROR_SEVERITY() AS ErrorSeverity,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage,getdate() as ErrorDateTime
   
    ROLLBACK TRANSACTION -- If this is commented out no messages are saved to any table, including _Table2 debug.
   
    INSERT INTO tblAWSObjectsErrorLog(ErrorNumber,ErrorState,ErrorSeverity,ErrorProcedure,ErrorLine,ErrorMessage,ErrorDateTime)
    SELECT [ErrorNumber],[ErrorState],[ErrorSeverity],[ErrorProcedure],[ErrorLine],[ErrorMessage], [ErrorDateTime]
    FROM
        @Trig_Error
   
   
   END CATCH
    INSERT INTO _Test2([Count],[Message])VALUES(0, 'AFTER tblAWSObjectsControl COMMAND')
    /*
    The WITH RESULT SETS NONE is required in order for the system stored procedure 
    sp_describe_first_result_set to not return an error
   */
   END;
   
   

In a nutshell.

• Database Trigger Creates a Trigger when Replicated table tblAWSObjects gets created.
• The new trigger inserts records into tblAWSObjectControl, only if it does not already exist by ID, see LEFT JOIN.
• tblAWSObjectControl, which only exists at the Target has a trigger to EXEC the command when a new record is created.

Now, 10 records get replicated, not sure the internals used of AWS/Data Migration Task; however, I do know that they are never sent in batch.

An exception occurs in EXEC(@Command). Instead of 10 records coming over with one error in the error log, no records are saved in tblAWSObject, no records are saved in tblAWSControl and only the debug trace that occurred after the first and only exception is saved.

_Test2(Debug)

ID	Message
0	tblAWSObjects trigger CREATED
0	AFTER tblAWSObjectsControl COMMAND
7	IN tblAWSObject CATCH
7	AFTER tblAWSObject INSERT

tblAWSObjectsErrorLog

ErrorMessage
Invalid object name 'dbo.xx'.

tblAWSObjects (empty)

tblAWSObjectsControl (empty)

EDIT :

It appears that AWS/DMS is using prepared statements for the inserts. The first 7/10 records I can see in the trace as:

exec sp_execute 1,1040,N'CREATE FUNCTION...'...
exec sp_execute 1,1041,N'CREATE FUNCTION...'...
exec sp_execute 1,1044,N'DROP VIEW...'...
exec sp_execute 1,1059,N'CREATE FUNCTION...'...
exec sp_execute 1,1060,N'CREATE FUNCTION...'...
exec sp_execute 1,1061,N'CREATE PROCEDURE...'...
exec sp_execute 1,1062,N'DROP PROCEDURE...'...
<--STOPS HERE-->

The prepared statements should be 10, however, they stop being sent after the 7th, the one resulting in EXEC failure. In all my wisdom, I would think that the TRY CATCH would not propagate an exception back to the AWS/DMS client, however, it still stops after 7 and nothing gets saved to the target table, not even the first 7. I am pretty sure there is some reconciliation process that I am missing.

EDIT:

If I change the block in LoadAWSObjects from:

BEGIN TRY               
    EXECUTE (@Command) WITH RESULT SETS NONE
END TRY

To:

BEGIN TRY               
    --EXECUTE (@Command) WITH RESULT SETS NONE
    DECLARE @X INT
    SET @X = 1 / 0
END TRY

Everything works as expected. The errors are logged and both tables are in synced.