For months I had done the whole data generation from within costly triggers, when the cost became very expensive I went back to using CDC but having done all kind of tricks in triggers initially opened my mind to combining triggers with CDC 🙂

Initially I started getting the DBCC Inputbuffer to get the sql statement that initiated the DML (during the trigger execution that is the only way to find the root DML), later on I realized I did not have much need for it and wanted to get out of the trigger as fast and removed it. To store the spid and analyze it later makes more sense, with no overhead during the trigger execution.

I wanted to point out the select statement with Before and After values at the end of the script, it's does the trick of displaying what's changed only in a more Audit like session, using the values clause to unpivot the data costing very few too.