DDL and DML Capture

1. Extended Events (XE) - as you stated, Extended Events is a viable option way to track all TRUNCATE, INSERT AND DELETE statements across the server without intrusive code changes. You can capture:

• sqlserver.sql_statement_completed
• sqlserver.sql_batch_completed

Then filter by statements containing INSERT, DELETE, TRUNCATE.

Example:

CREATE EVENT SESSION [Track_DML] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.session_id)
    WHERE (sqlserver.sql_text LIKE '%INSERT%' 
        OR sqlserver.sql_text LIKE '%DELETE%' 
        OR sqlserver.sql_text LIKE '%TRUNCATE%')
)
ADD TARGET package0.event_file (SET filename = 'C:\XE\Track_DML.xel', max_file_size = 100);
GO

ALTER EVENT SESSION [Track_DML] ON SERVER STATE = START;

The benefits include:

• Centralized, lightweight, server-level coverage.
• Minimal performance overhead.
• No schema changes required.
• Works across all databases.

On the down side:

• You get statement-level text, not the affected rows or before/after data.
• Complex to filter “only real user” DMLs vs. system/internal ones.
• TRUNCATE is captured as a statement, but not as a DML triggerable event.

2. Change Data Capture (CDC) or Temporal Tables, which give you row-level auditing

• CDC (Enterprise Edition, or Standard from SQL 2016 SP1+) captures INSERT, DELETE, and row versions for UPDATE.
• Temporal Tables give you a full version history (for supported tables).

Effectively, you get:

• Full data-level audit trail.
• Natively supported; integrates with queries.

There are some drawbacks though:

• Must enable per table.
• Doesn't track TRUNCATE TABLE (as it bypasses CDC).
• Adds storage overhead.

3. Database-level DML Triggers – Per-table Granular Auditing

These can track data mutations and user context (HOST_NAME(), APP_NAME(), SUSER_SNAME()), but only for the specific table.

This gives you the following benefits:

• Fine-grained control and access to the data itself.
• Can write to your existing audit table.

The drawbacks include:

• Maintenance burden (needs adding to every table).
• Can impact write performance.
• Still cannot capture TRUNCATE (no trigger fires).

4. SQL Server Audit (Enterprise / Standard in newer builds)

You can configure an Audit Specification to log DMLs at the server or database level.

Example audited actions:

• INSERT, UPDATE, DELETE on a table or schema.

Pluses:

• Built-in, tamper-resistant audit trail.
• Can audit specific operations per object.
• Centralized in .sqlaudit files or event logs.

Drawbacks:

• No TRUNCATE auditing (treated as DDL).
• Enterprise Edition used to be required for fine-grained DML auditing (Standard now supports some in newer builds).

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin