You need a durable trail of who changed which rows in a set of business tables. Compare writing audit rows from AFTER-row triggers against building the trail from the database's transaction log with change data capture, and say when each is the right choice.

## The two mechanisms **Trigger-based audit trails.** For each table you want history on, you attach an AFTER INSERT/UPDATE/DELETE row-level trigger that inserts a record into a companion history table — typically the old row image, the new row image, the action, the timestamp, the database login, and (importantly) whatever end-user identity the application stashed in the session. The audit row is written in the same transaction as the change. **Log-based change data capture.** Every relational engine already writes a durable, ordered record of committed changes for crash recovery and replication (the write-ahead log, binlog, or redo). A CDC reader attaches as if it were a replica, decodes those records into logical row changes, and streams them to a collector, topic, or table outside the database. ## Where the cost lands Triggers put the cost **in the transaction**. Every audited write becomes at least two writes: the base row and the history row, each of which also generates transaction-log traffic, index maintenance and, over time, table bloat and vacuum/purge work. On a hot table this is a measurable latency tax on the user-facing path, and it is paid by the same connections serving requests. Bulk operations feel it worst: a million-row update becomes two million row writes plus a million trigger invocations. CDC puts the cost **outside the transaction**. The log is being written anyway; the reader is another consumer of it. The write path pays nothing beyond keeping the relevant log segments around long enough for the reader to consume them — which is exactly where CDC's operational risk lives (below). ## Coverage: what each mechanism can and cannot see Triggers see only what fires them. The standard blind spots: - **TRUNCATE** does not fire row triggers (a statement-level trigger is needed, and it has no row images). - **Bulk loaders and direct-path/copy utilities** may bypass triggers by design. - **Replication apply** on a replica may not fire triggers unless explicitly enabled, so history diverges between primary and replica. - **A privileged user can disable the trigger, make the change, and re-enable it** — leaving a clean history table. Unless the ALTER that disabled it was itself audited by the engine, the trail lies. - **Rolled-back transactions leave nothing**, because the audit row rolls back with the change. For data lineage that is correct; for detecting attempted tampering it is a gap. CDC sees every **committed** change to data, including bulk operations and anything a superuser does to table contents, because the recovery log cannot be bypassed without corrupting the database. But it does not see uncommitted or rolled-back work, and — depending on engine and configuration — DDL is often visible only as an opaque or partial event. Critically, **neither mechanism records reads.** A SELECT touches no rows and produces no log record. If the compliance requirement is "prove who *looked at* this data", both are the wrong tool and you need engine-side audit rules on the object. ## Attribution This is where triggers win. A trigger runs in the session that made the change, so it can read a session context variable, a GUC, or the client application name that the application set at request start, and write the *human* actor into the history row. CDC sees the transaction as recorded in the log: the database login, sometimes the transaction id and commit timestamp, and little else — behind a connection pool that means `app_user` for everything. The standard fix on the CDC side is to make identity part of the data: have the application write a small marker row (request id, end-user id, reason) in the same transaction, or carry `updated_by`/`updated_at` columns on the table itself, so the identity flows through the log with the change and the consumer can stitch it together. Say this in an interview — it is the detail that shows you have designed one. ## Operational failure modes For triggers: schema drift is the chronic one. A column added to the base table and forgotten in the history table silently stops being captured; generic "capture the whole row as a document" designs avoid that at the cost of queryability. Purging history is the other — history tables outgrow their parents, so partition by time and drop partitions rather than issuing large DELETEs. For CDC: the reader's position pins the log. If the consumer stalls — a crash, a slow sink, a schema the decoder cannot parse — the engine must retain log segments from that position onward, and the primary's disk fills. A full log volume takes the whole database down, so a CDC audit pipeline needs lag alerting and a documented policy for when to drop the reader and re-bootstrap. Schema evolution is the second: the decoder must map old log records written under an older table shape. ## Choosing - **Use CDC** when you need a complete, low-overhead history of committed data changes across many tables, want it off-box for tamper resistance, and can tolerate seconds of lag and coarse actor attribution improved by in-band identity columns. - **Use triggers** for a small number of tables where the history must be transactionally consistent with the change, must carry the end-user identity synchronously, and must be queryable with a plain join right next to the data. - **Use engine auditing** — not either of these — for logins, DDL, privilege changes, failed attempts, and read access. Most mature systems run all three, each on the job it is actually good at.