SQL server CDC to get changes in primary and foreign tables in a single transaction

I have a scenario to capture the auditing changes in system. I have开发者_如何学运维 two tables Primary Table and Foreign table with one to many relation. My system making data auditing as one record in primary table and more than 10 records chagnes in foregin table in a single transaction.

CDC is giving the chagnes in two records individually, how to get the changes in two tables using group by time.

I need to make a report on auditing columns information on both tables group by a transaction time.

Sorry, description is in theory but not sure how to present with data.

I have a vague idea that this isn't directly possible using LSNs, and this forum posting seems to indicate this is the case. The only other option that comes to mind is to use database triggers and record transaction identifiers onto audit log records in explicitly managed audit log tables. You can get a transaction identifer within a trigger from sys.dm_tran_current_transacton.