The right mechanism follows from three questions: does the consumer need old values or every intermediate change, must changes be pushed or can they be polled, and which platform hosts the database. Figure 12.1 turns the first two questions into a decision tree.
| Requirement | Best fit | Why not the alternative |
|---|---|---|
| Publish every row change to several downstream consumers in near real time | CES to Event Hubs or Fabric Eventstream | Polling options make each consumer query the database |
| Incremental ETL that needs before and after values or full history | CDC | Change tracking keeps no old values |
| Keep a cache or mobile client synchronised with current values, with conflict detection | Change tracking with CHANGETABLE |
CDC stores far more than needed |
| Run code on each change without hosting a poller | Azure Functions SQL trigger | A custom loop over CHANGETABLE is code to maintain |
| Start a low-code workflow (email, approvals, other SaaS connectors) on a new or changed row | Logic Apps SQL Server trigger | Functions needs code and deployment |
| Track changes in SQL database in Fabric | Change tracking (or CES, preview) | CDC isn’t supported |
| Capture deletes in a Logic Apps workflow | Built-in trigger in a Standard logic app | Managed triggers have no delete trigger |
Some combinations are blocked: CES can’t share a database with CDC or transactional replication, while change tracking coexists with both. Where an inline reaction inside the writing transaction is truly required, a T-SQL DML trigger (Chapter 3, “Programmability objects and error handling”) runs synchronously, but it adds its work, and any failure, to the user’s transaction; the mechanisms in this chapter keep that work out of the write path.
Common trap: Picking CES for a requirement to load a target with all existing rows plus future changes - CES never sends rows that existed before it was enabled, so an initial load has to be done separately.