Since there's been some ongoing interest to this and years later I've become quite well acquainted with all of the above, here's a short summary in performance terms: I did a test on some version of SQL Server 2016 involving inserting, updating, and deleting 10000 rows from 40 different types of tables one by one, and charted the overall time spent, basic locking info etc. about each. The simple summary is that where triggers added on average 500-1000% more delay to the operations, with temporal tables and CDC it was closer to 10% extra delay per operation. Would help if I had the exact results but I don't remember them anymore. The trigger process was very streamlined, but inserted one row per changed column, vs. temporal / cdc which inserted one row regardless of how many columns in it were changed. In this sense, some changes could have made the triggers seem slower than they were, because of the key contention of multiple rows being inserted at the same time. Nevertheless, it was obvious triggers were the least suited tool for simple auditing. So here's a simple technical rundown of the differences I was trying to understand when I created this post:
Triggers are only ever good if you really need some custom logic built into the DB, to watch over DML changes, modify specific data, capture the userid in specific instances, etc. But try to avoid them like the plague. They're horrible for performance. And if you need auditing or logging, they're the last place you should look.
Temporal tables are very easy to manage once you get them running especially in HADR like Always On. As they support compression and reflect most schema changes from parent to history table, so they require very little upkeep. Especially with new SQL Server versions you can set the retention period to remove data older than x years anyway so the storage and cleanup considerations are negligible as well. They are as fire & forget as things come, barring some exotic updates to parent tables where you need to change the data, in which case you have to de-link, modify the parent and history table, and link them again. But these are rare and relatively easy to do. The temporal table package is robust and handles errors well so you'll find it hard to break by accident.
CDC then can be great for reporting services or similar scenarios where you don't mind asynchronous data, but you need to analyze changes for example in nightly batches. You can set the retention setting to only keep x days of data to keep the storage costs to a minimum. That said, CDC is to my experience finicky and not very stable. DML's can "break" it sometimes with no warning, so you might need db-level DDL triggers to warn you of changes to objects tracked by CDC. You might also need to set custom watchjobs for HADR as it does not natively handle failover events. And CDC has a very nasty propensity for failing to restart after being disabled, something about the state of it not being updated correctly using MS own jobs. This means it will occasionally require manual work to make sure the cleanup and capture jobs and their references are removed correctly. That said, SSIS / RS integrate very well and make using CDC easy for them.
