Get all changed tables from CDC
Instead of using triggers on the CDC tables, you should use the built-in CDC functions for returning the changes recorded by CDC. If you only need the latest value for each row, use cdc.fn_cdc_get_net_changes but if you need all intermediate changes as well, use cdc.fn_cdc_get_all_changes.

These functions are created for each table (or 'capture instance') that you enable CDC on and the functions return changes on a table from the earliest LSN to the latest LSN. There is a good article here that delves into CDC and retrieving the data.

To use it in your periodic polling process, you could do something like this:


Fetch the earliest LSN from cdc.lsn_time_mapping after the last datetime that you polled.
For each table, fetch the max lsn value using sys.fn_cdc_get_max_lsn
Use cdc.fn_cdc_get_net_changes or cdc.fn_cdc_get_all_changes with the LSNs from step 1 and 2 to fetch the changes that have occurred since the last polling.
Process the changes into MongoDB
Log the min and max LSN and the start and end datetime used during this polling to a table. This can be used on the next poll to get the datetime value to determine your next min LSN.


You could even use SQL Server Integration Services which has built-in tools for working with CDC data.