A value changed, and the table has no reliable history. Auditing data changes starts by deciding what the answer must contain before choosing a SQL Server feature.
Define the Question Before Auditing Data Changes
Who changed a row, what changed, and when are three different questions. A temporal table can preserve older row versions, but it does not automatically identify the application user. SQL Server Audit can capture selected actions, while a trigger can capture before and after values. CDC provides a change feed for consumers.
I ask what evidence is required for support, compliance, or synchronization. If the requirement is only to feed downstream changes, an actor field is not central. If the requirement names a person, a shared application login is not enough. The identity source must be trustworthy.
Write retention, access, and tamper resistance into the requirement. An audit table in the same database can be altered by privileged users. The design should match the strength of the claim it is expected to support.
SELECT name, temporal_type_desc
FROM sys.tables
WHERE temporal_type <> 0
ORDER BY name;Use Triggers for Custom Row Detail
A DML trigger can read inserted and deleted sets to capture before and after values. It must handle multi-row statements. A trigger written for one row can miss changes or fail when an import updates a batch. Keep its work narrow and transactionally safe.
I test insert, update, delete, and rollback. Trigger writes occur in the same transaction as the data change, so a rollback should remove both. That can be desirable, but it means a trigger error can block the business write. Measure the write overhead.
For actor context, capture the database login and a trusted application user identifier if the application supplies one. Do not trust an arbitrary session value without controlling who can set it. An audit is only as strong as its identity path.
SELECT name, is_disabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.Customer')
ORDER BY name;Use Temporal Tables for Row Versions
System-versioned temporal tables retain prior row versions with validity periods. They are useful for asking what a row looked like at a time. They add history storage and affect write operations. They do not, by themselves, say which person made the change.
I check whether the application needs point-in-time reconstruction or a detailed event log. Temporal history answers the former well. The query below assumes dbo.Customer is temporal and names its period columns ValidFrom and ValidTo. A business event such as “approved by reviewer” can need its own explicit record even when every column version is retained.
Test retention and access to the history table. A long-lived history can become large, and users who can read current data should not automatically be assumed to have the right history permissions.
SELECT CustomerId, CustomerName, ValidFrom, ValidTo
FROM dbo.Customer FOR SYSTEM_TIME ALL
WHERE CustomerId = 42
ORDER BY ValidFrom;
Use CDC for a Change Feed
Change Data Capture reads committed changes from the transaction log into change tables. It can record operations and before or after values according to configuration. It is useful for downstream integration, but its retention window and capture jobs need monitoring.
I check capture lag and the minimum available LSN before a consumer reads. If retention removes changes before the consumer catches up, the pipeline needs a new baseline. A green SQL Agent job alone does not prove the consumer received every change.
CDC is not a complete who-did-it audit. The change table’s metadata does not automatically map each row to the end user behind a shared application login. Use a separate trusted identity record if that answer is required.
EXEC sys.sp_cdc_help_change_data_capture;Use SQL Server Audit for Selected Actions
SQL Server Audit can record selected server or database actions with configured targets. It is valuable for access and action evidence. It needs a carefully scoped specification, target protection, and retention plan. Capturing every action can create volume without a useful question.
I test the exact action and principal required by the audit policy. A broad login event does not prove who changed one business row. Pair audit events with row-level history when both questions matter.
Protect audit targets from the accounts being audited where the security model requires it. A record that can be altered unnoticed is weak evidence. Review failure behavior if the audit target becomes unavailable.
SELECT name, is_state_enabled, on_failure_desc
FROM sys.server_audits
ORDER BY name;Compare Cost and Coverage for Auditing Data Changes
Triggers add synchronous write work. Temporal tables add history rows and storage. CDC adds capture processing and retention management. SQL Server Audit adds event volume and target operations. No feature for auditing data changes is free, and none answers every question alone.
I pilot one representative table with batch writes and read queries. Measure overhead and confirm the actual fields captured. A design that performs well on single-row updates can behave differently during a nightly import.
Use a matrix of required evidence: old value, new value, time, actor, transaction, and retention. Mark which feature supplies each field under your configuration. Do not fill gaps with assumptions.
SELECT name, is_tracked_by_cdc, temporal_type_desc
FROM sys.tables
WHERE name = N'Customer';Test Auditing Data Changes Before Declaring Success
Change a test row under a known application identity, then retrieve the audit evidence without relying on memory. Can you show old and new values, time, and actor as required? Repeat with a batch update and a rollback. Check what happens when retention expires.
I keep the retrieval query and runbook beside the audit design. An audit system that nobody can query during an incident is an expensive archive. The output should support a precise statement, not a guess about a login.
Auditing data changes is a choice about evidence. Pick the mechanism from the question, secure the identity path, and measure the write and storage cost. Then test the answer before the next real investigation.
SELECT ORIGINAL_LOGIN() AS OriginalLogin,
SUSER_SNAME() AS CurrentLogin;An audit design needs an answer to who, what, when, and why. A trigger can record application context if the application supplies trustworthy context, but a shared service login alone does not identify an end user. Which identity is the business asking for? I make that distinction before writing an audit table. A field called ChangedBy does not become reliable merely because it has a value.
Test bulk updates, failures, rollbacks, and privileged changes. A trigger runs in the same transaction as the statement, while temporal history and CDC expose different parts of the story. Retention and access control matter because audit rows can contain sensitive old values. If an audit is evidence for a formal review, agree on tamper controls and independent storage with the responsible team.
Related reading on this blog: Temporal Tables: Keeping History Without a Trigger and How to Capture Deleted Rows Without Trigger? Interview Question of the Week #297.
An audit feature is not an answer to who changed a row, it is a way to collect specified evidence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
