How to Trace Trigger Executions with Query Store and Extended Events

January 5, 2026

42 min read

Category: query-performance , sql-server

How to Trace Trigger Executions with Query Store and Extended Events

Triggers can be tricky to observe in SQL Server. When you need to understand exactly what a trigger is doing and what it impacts, Query Store and Extended Events both have something to offer, but using them effectively requires navigating some confusing nuances. Query Store tracks query-level executions aggregated by query_id, but has a habit of generating a lot of query_ids with different context settings for triggers. Extended Events can capture trigger module starts and individual statement completions within triggers.

This post walks through setting up both observation methods on a test trigger, then compares what each one shows. You’ll see how Query Store data gets spread across multiple query_ids and context_settings_id values, a variety of ways to trace triggers in XEvents, and why the numbers don’t always match up exactly between traces and Query Store.

I’m using the StackOverflow2013 sample database on SQL Server 2025 Developer Edition to create a trigger, test it with various DML operations (INSERT, UPDATE, DELETE, MERGE, and dynamic SQL), then analyze the results from both observation methods.

Setup: Enable and Clear Query Store

First, let’s make sure Query Store is enabled. This clears all data from Query Store so be careful where you run this.

USE StackOverflow2013;
GO

ALTER DATABASE CURRENT
SET QUERY_STORE = ON;
GO

ALTER DATABASE CURRENT
SET QUERY_STORE
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 10240,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14)
);
GO

/* WARNING: Clear Query Store data for clean testing */
/* This removes all Query Store data - use with caution in production */
ALTER DATABASE CURRENT
SET QUERY_STORE CLEAR;
GO

Setup: Create the Audit Table

Next, create a simple audit table to track changes to the Posts table:

IF OBJECT_ID(N'dbo.AuditPosts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.AuditPosts;
END;
GO

CREATE TABLE
    dbo.AuditPosts
(
    AuditPostsId bigint NOT NULL IDENTITY(1,1),
    PostsId integer NOT NULL,
    Action char(1) NOT NULL,
    ActivityDate datetime NOT NULL,
    ColumnName sysname NOT NULL,
    ColumnValue nvarchar(250) NULL,
    CONSTRAINT PK_AuditPosts_AuditPostsId
        PRIMARY KEY CLUSTERED (AuditPostsId)
);
GO

Setup: Create the Trigger on Posts

Now I’m creating a wild and safety-free trigger on the Posts table. This trigger has no safety checks (no IF EXISTS blocks) and uses UNION ALL with separate queries for each column to handle NULLs.

When we modify the Posts table (INSERT, UPDATE, or DELETE), the trigger automatically fires and performs these actions:

When we modify Posts	Trigger behavior
INSERT or UPDATE	Trigger updates `LastActivityDate` on the Posts table
INSERT	Trigger adds 4 rows to AuditPosts (one for each of Title, PostTypeId, OwnerUserId, Score) with Action = ‘I’, using separate UNION ALL queries that handle NULL values for Title and OwnerUserId
UPDATE	Trigger adds 4 rows to AuditPosts with Action = ‘U’ if any of Title, PostTypeId, OwnerUserId, or Score changed (inserts all 4 rows with “before” values from the deleted table when any column changes)
DELETE	Trigger adds 4 rows to AuditPosts with Action = ‘D’, using separate UNION ALL queries that handle NULL Title for answers

Important: All modifications to AuditPosts come from the trigger. We never directly INSERT, UPDATE, or DELETE from AuditPosts. We only modify Posts, and the trigger handles all AuditPosts changes automatically.

By “safety-free”, I mean this trigger lacks the Good Things in Trigger Life:

It’s not selectively firing.
It does not check if ROWCOUNT_BIG() = 0 and return if nothing happens.
It doesn’t do any of the following:
- Existence checks (no EXISTS or NOT EXISTS clauses) to see if the inserted and deleted tables have rows in them
- Use the UPDATE() trigger function
- Use the COLUMNS_UPDATED() trigger function to test if the trigger should execute certain actions

Instead, it uses only NULL comparisons to distinguish between INSERT, UPDATE, and DELETE operations. For INSERT operations, it joins inserted to deleted using LEFT JOIN and check if deleted.Id IS NULL. For DELETE operations, we check if inserted.Id IS NULL when joining deleted to inserted.

IF OBJECT_ID(N'dbo.tr_Posts_Audit_No_Safeties', N'TR') IS NOT NULL
BEGIN
    DROP TRIGGER dbo.tr_Posts_Audit_No_Safeties;
END;
GO

CREATE TRIGGER
    dbo.tr_Posts_Audit_No_Safeties
ON dbo.Posts
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    /* Update LastActivityDate on INSERT and UPDATE */
    UPDATE
        p
    SET
        p.LastActivityDate = GETDATE()
    FROM dbo.Posts AS p
    JOIN inserted AS i
      ON i.Id = p.Id;
    /* Handle INSERT - only when deleted table is empty */
    INSERT INTO
        dbo.AuditPosts
    (
        PostsId, Action, ActivityDate, ColumnName, ColumnValue
    )
    SELECT
        PostsId = i.Id,
        Action = 'I',
        ActivityDate = GETDATE(),
        ColumnName = N'Title',
        ColumnValue = CONVERT(nvarchar(250), i.Title)
    FROM inserted AS i
    WHERE NOT EXISTS
    (
        SELECT
            1/0
        FROM deleted AS d
    )
    AND i.Title IS NOT NULL

    UNION ALL

    SELECT
        PostsId = i.Id,
        Action = 'I',
        ActivityDate = GETDATE(),
        ColumnName = N'Title',
        ColumnValue = NULL
    FROM inserted AS i
    WHERE NOT EXISTS
    (
        SELECT
            1/0
        FROM deleted AS d
    )
    AND i.Title IS NULL

    UNION ALL

    SELECT
        PostsId = i.Id,
        Action = 'I',
        ActivityDate = GETDATE(),
        ColumnName = N'PostTypeId',
        ColumnValue = CONVERT(nvarchar(250), i.PostTypeId)
    FROM inserted AS i
    WHERE NOT EXISTS
    (
        SELECT
            1/0
        FROM deleted AS d
    )

    UNION ALL

    SELECT
        PostsId = i.Id,
        Action = 'I',
        ActivityDate = GETDATE(),
        ColumnName = N'OwnerUserId',
        ColumnValue = CONVERT(nvarchar(250), i.OwnerUserId)
    FROM inserted AS i
    WHERE NOT EXISTS
    (
        SELECT
            1/0
        FROM deleted AS d
    )
    AND i.OwnerUserId IS NOT NULL

    UNION ALL

    SELECT
        PostsId = i.Id,
        Action = 'I',
        ActivityDate = GETDATE(),
        ColumnName = N'OwnerUserId',
        ColumnValue = NULL
    FROM inserted AS i
    WHERE NOT EXISTS
    (
        SELECT
            1/0
        FROM deleted AS d
    )
    AND i.OwnerUserId IS NULL

    UNION ALL

    SELECT
        PostsId = i.Id,
        Action = 'I',
        ActivityDate = GETDATE(),
        ColumnName = N'Score',
        ColumnValue = CONVERT(nvarchar(250), i.Score)
    FROM inserted AS i
    WHERE NOT EXISTS
    (
        SELECT
            1/0
        FROM deleted AS d
    );
    /* Handle UPDATE - only if Title, PostTypeId, OwnerUserId, or Score changed */
    INSERT INTO
        dbo.AuditPosts
    (
        PostsId, Action, ActivityDate, ColumnName, ColumnValue
    )
    SELECT
        PostsId = d.Id,
        Action = 'U',
        ActivityDate = GETDATE(),
        ColumnName = N'Title',
        ColumnValue = CONVERT(nvarchar(250), d.Title)
    FROM inserted AS i
    JOIN deleted AS d
      ON d.Id = i.Id
    WHERE
    (
        ISNULL(i.Title, N'') <> ISNULL(d.Title, N'')
        OR i.PostTypeId <> d.PostTypeId
        OR ISNULL(i.OwnerUserId, 0) <> ISNULL(d.OwnerUserId, 0)
        OR i.Score <> d.Score
    )
    AND d.Title IS NOT NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'U',
        ActivityDate = GETDATE(),
        ColumnName = N'Title',
        ColumnValue = NULL
    FROM inserted AS i
    JOIN deleted AS d
      ON d.Id = i.Id
    WHERE
    (
        ISNULL(i.Title, N'') <> ISNULL(d.Title, N'')
        OR i.PostTypeId <> d.PostTypeId
        OR ISNULL(i.OwnerUserId, 0) <> ISNULL(d.OwnerUserId, 0)
        OR i.Score <> d.Score
    )
    AND d.Title IS NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'U',
        ActivityDate = GETDATE(),
        ColumnName = N'PostTypeId',
        ColumnValue = CONVERT(nvarchar(250), d.PostTypeId)
    FROM inserted AS i
    JOIN deleted AS d
      ON d.Id = i.Id
    WHERE
    (
        ISNULL(i.Title, N'') <> ISNULL(d.Title, N'')
        OR i.PostTypeId <> d.PostTypeId
        OR ISNULL(i.OwnerUserId, 0) <> ISNULL(d.OwnerUserId, 0)
        OR i.Score <> d.Score
    )

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'U',
        ActivityDate = GETDATE(),
        ColumnName = N'OwnerUserId',
        ColumnValue = CONVERT(nvarchar(250), d.OwnerUserId)
    FROM inserted AS i
    JOIN deleted AS d
      ON d.Id = i.Id
    WHERE
    (
        ISNULL(i.Title, N'') <> ISNULL(d.Title, N'')
        OR i.PostTypeId <> d.PostTypeId
        OR ISNULL(i.OwnerUserId, 0) <> ISNULL(d.OwnerUserId, 0)
        OR i.Score <> d.Score
    )
    AND d.OwnerUserId IS NOT NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'U',
        ActivityDate = GETDATE(),
        ColumnName = N'OwnerUserId',
        ColumnValue = NULL
    FROM inserted AS i
    JOIN deleted AS d
      ON d.Id = i.Id
    WHERE
    (
        ISNULL(i.Title, N'') <> ISNULL(d.Title, N'')
        OR i.PostTypeId <> d.PostTypeId
        OR ISNULL(i.OwnerUserId, 0) <> ISNULL(d.OwnerUserId, 0)
        OR i.Score <> d.Score
    )
    AND d.OwnerUserId IS NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'U',
        ActivityDate = GETDATE(),
        ColumnName = N'Score',
        ColumnValue = CONVERT(nvarchar(250), d.Score)
    FROM inserted AS i
    JOIN deleted AS d
      ON d.Id = i.Id
    WHERE
    (
        ISNULL(i.Title, N'') <> ISNULL(d.Title, N'')
        OR i.PostTypeId <> d.PostTypeId
        OR ISNULL(i.OwnerUserId, 0) <> ISNULL(d.OwnerUserId, 0)
        OR i.Score <> d.Score
    );
    /* Handle DELETE - use LEFT JOIN to inserted and check if inserted.Id IS NULL */
    INSERT INTO
        dbo.AuditPosts
    (
        PostsId, Action, ActivityDate, ColumnName, ColumnValue
    )
    SELECT
        PostsId = d.Id,
        Action = 'D',
        ActivityDate = GETDATE(),
        ColumnName = N'Title',
        ColumnValue = CONVERT(nvarchar(250), d.Title)
    FROM deleted AS d
    LEFT JOIN inserted AS i
      ON i.Id = d.Id
    WHERE i.Id IS NULL
    AND d.Title IS NOT NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'D',
        ActivityDate = GETDATE(),
        ColumnName = N'Title',
        ColumnValue = NULL
    FROM deleted AS d
    LEFT JOIN inserted AS i
      ON i.Id = d.Id
    WHERE i.Id IS NULL
    AND d.Title IS NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'D',
        ActivityDate = GETDATE(),
        ColumnName = N'PostTypeId',
        ColumnValue = CONVERT(nvarchar(250), d.PostTypeId)
    FROM deleted AS d
    LEFT JOIN inserted AS i
      ON i.Id = d.Id
    WHERE i.Id IS NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'D',
        ActivityDate = GETDATE(),
        ColumnName = N'OwnerUserId',
        ColumnValue = CONVERT(nvarchar(250), d.OwnerUserId)
    FROM deleted AS d
    LEFT JOIN inserted AS i
      ON i.Id = d.Id
    WHERE i.Id IS NULL
    AND d.OwnerUserId IS NOT NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'D',
        ActivityDate = GETDATE(),
        ColumnName = N'OwnerUserId',
        ColumnValue = NULL
    FROM deleted AS d
    LEFT JOIN inserted AS i
      ON i.Id = d.Id
    WHERE i.Id IS NULL
    AND d.OwnerUserId IS NULL

    UNION ALL

    SELECT
        PostsId = d.Id,
        Action = 'D',
        ActivityDate = GETDATE(),
        ColumnName = N'Score',
        ColumnValue = CONVERT(nvarchar(250), d.Score)
    FROM deleted AS d
    LEFT JOIN inserted AS i
      ON i.Id = d.Id
    WHERE i.Id IS NULL;
END;
GO

Setup: Create Query Store History Capture Helper

To track which Query Store query IDs correspond to which operations, we’ll create a helper procedure that captures Query Store data after each step in the test procedure. This eliminates guesswork when analyzing Query Store output.

The helper procedure queries Query Store for all trigger-related queries and saves them to a history table with a step description. This lets us see exactly which query IDs were created or updated after each operation.

/* Create table to store Query Store history with step descriptions */
IF OBJECT_ID(N'dbo.QueryStoreHistory', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.QueryStoreHistory;
END;
GO

CREATE TABLE
    dbo.QueryStoreHistory
(
    history_id bigint NOT NULL IDENTITY(1,1) PRIMARY KEY,
    step_number integer NOT NULL,
    step_description nvarchar(100) NOT NULL,
    query_id bigint NOT NULL,
    query_text_id bigint NOT NULL,
    plan_id bigint NOT NULL,
    runtime_stats_id bigint NOT NULL,
    context_settings_id bigint NOT NULL,
    set_options_decoded nvarchar(max) NOT NULL,
    query_sql_text nvarchar(max) NOT NULL,
    count_executions bigint NOT NULL,
    count_compiles bigint NOT NULL,
    avg_cpu_time decimal(38,2) NOT NULL,
    avg_duration decimal(38,2) NOT NULL,
    avg_logical_io_reads decimal(38,2) NOT NULL,
    last_rowcount bigint NOT NULL,
    min_rowcount bigint NOT NULL,
    max_rowcount bigint NOT NULL,
    stdev_rowcount decimal(38,2) NOT NULL,
    last_execution_time datetimeoffset(7) NOT NULL,
    captured_at datetime2(7) NOT NULL DEFAULT SYSDATETIME()
);
GO

/* Helper procedure to capture Query Store data for trigger queries */
CREATE OR ALTER PROCEDURE
    dbo.sp_CaptureQueryStoreHistory
(
    @step_number integer, /* Step number for ordering (1, 2, 3, etc.) */
    @step_description nvarchar(100) /* Description of what step just executed */
)
AS
BEGIN
    SET NOCOUNT ON;

    /* Strip HTML tags from step description before storing */
    DECLARE
        @clean_description nvarchar(100) = @step_description;
    
    /* Remove common HTML tags */
    SET @clean_description = REPLACE(@clean_description, N'<strong>', N'');
    SET @clean_description = REPLACE(@clean_description, N'</strong>', N'');

    INSERT INTO
        dbo.QueryStoreHistory
    (
        step_number,
        step_description,
        query_id,
        query_text_id,
        plan_id,
        runtime_stats_id,
        context_settings_id,
        set_options_decoded,
        query_sql_text,
        count_executions,
        count_compiles,
        avg_cpu_time,
        avg_duration,
        avg_logical_io_reads,
        last_rowcount,
        min_rowcount,
        max_rowcount,
        stdev_rowcount,
        last_execution_time
    )
    SELECT
        step_number = @step_number,
        step_description = @clean_description,
        query_id = latest_stats.query_id,
        query_text_id = ISNULL(latest_stats.query_text_id, 0),
        plan_id = ISNULL(latest_stats.plan_id, 0),
        runtime_stats_id = ISNULL(latest_stats.runtime_stats_id, 0),
        context_settings_id = ISNULL(latest_stats.context_settings_id, 0),
        set_options_decoded = latest_stats.set_options_decoded,
        query_sql_text = ISNULL(latest_stats.query_sql_text, N''),
        count_executions = ISNULL(latest_stats.count_executions, 0),
        count_compiles = ISNULL(latest_stats.count_compiles, 0),
        avg_cpu_time = ISNULL(latest_stats.avg_cpu_time, 0),
        avg_duration = ISNULL(latest_stats.avg_duration, 0),
        avg_logical_io_reads = ISNULL(latest_stats.avg_logical_io_reads, 0),
        last_rowcount = ISNULL(latest_stats.last_rowcount, 0),
        min_rowcount = ISNULL(latest_stats.min_rowcount, 0),
        max_rowcount = ISNULL(latest_stats.max_rowcount, 0),
        stdev_rowcount = ISNULL(latest_stats.stdev_rowcount, 0),
        last_execution_time = ISNULL(latest_stats.last_execution_time, CAST('1900-01-01' AS datetimeoffset(7)))
    FROM
    (
        /* Get latest runtime stats entry for each plan */
        SELECT
            qsq.query_id,
            qsq.query_text_id,
            qsp.plan_id,
            qsrs.runtime_stats_id,
            qsq.context_settings_id,
            set_options_decoded = 
                ISNULL
                (
                    STUFF
                    (
                        CASE WHEN (qcs.set_options & 1) = 1 THEN ', ANSI_PADDING ON' ELSE ', ANSI_PADDING OFF' END +
                        CASE WHEN (qcs.set_options & 2) = 2 THEN ', ANSI_NULLS ON' ELSE ', ANSI_NULLS OFF' END +
                        CASE WHEN (qcs.set_options & 4) = 4 THEN ', QUOTED_IDENTIFIER ON' ELSE ', QUOTED_IDENTIFIER OFF' END +
                        CASE WHEN (qcs.set_options & 8) = 8 THEN ', ARITHABORT ON' ELSE ', ARITHABORT OFF' END +
                        CASE WHEN (qcs.set_options & 16) = 16 THEN ', ARITHIGNORE ON' ELSE ', ARITHIGNORE OFF' END +
                        CASE WHEN (qcs.set_options & 32) = 32 THEN ', NUMERIC_ROUNDABORT ON' ELSE ', NUMERIC_ROUNDABORT OFF' END +
                        CASE WHEN (qcs.set_options & 64) = 64 THEN ', ANSI_WARNINGS ON' ELSE ', ANSI_WARNINGS OFF' END +
                        CASE WHEN (qcs.set_options & 128) = 128 THEN ', ANSI_NULL_DFLT_ON ON' ELSE ', ANSI_NULL_DFLT_ON OFF' END +
                        CASE WHEN (qcs.set_options & 256) = 256 THEN ', CONCAT_NULL_YIELDS_NULL ON' ELSE ', CONCAT_NULL_YIELDS_NULL OFF' END +
                        CASE WHEN (qcs.set_options & 512) = 512 THEN ', CURSOR_CLOSE_ON_COMMIT ON' ELSE ', CURSOR_CLOSE_ON_COMMIT OFF' END +
                        CASE WHEN (qcs.set_options & 1024) = 1024 THEN ', IMPLICIT_TRANSACTIONS ON' ELSE ', IMPLICIT_TRANSACTIONS OFF' END +
                        CASE WHEN (qcs.set_options & 2048) = 2048 THEN ', TEXTSIZE 2147483647' ELSE ', TEXTSIZE default' END +
                        CASE WHEN (qcs.set_options & 4096) = 4096 THEN ', DATE FIRST 1' ELSE ', DATE FIRST 7' END +
                        CASE WHEN (qcs.set_options & 8192) = 8192 THEN ', DATE FORMAT mdy' ELSE ', DATE FORMAT dmy' END +
                        CASE WHEN (qcs.set_options & 16384) = 16384 THEN ', LANGUAGE us_english' ELSE ', LANGUAGE other' END,
                        1, 2, ''
                    ),
                    N'UNKNOWN'
                ),
            query_sql_text = qst.query_sql_text,
            qsrs.count_executions,
            qsq.count_compiles,
            qsrs.avg_cpu_time,
            qsrs.avg_duration,
            qsrs.avg_logical_io_reads,
            qsrs.last_rowcount,
            qsrs.min_rowcount,
            qsrs.max_rowcount,
            qsrs.stdev_rowcount,
            qsrs.last_execution_time,
            row_number = 
                ROW_NUMBER() OVER
                (
                    PARTITION BY
                        qsp.plan_id
                    ORDER BY
                        qsrs.runtime_stats_id DESC
                )
        FROM sys.query_store_query AS qsq
        JOIN sys.query_store_query_text AS qst 
          ON qsq.query_text_id = qst.query_text_id
        JOIN sys.query_store_plan AS qsp
          ON qsp.query_id = qsq.query_id
        JOIN sys.query_store_runtime_stats AS qsrs
          ON qsrs.plan_id = qsp.plan_id
        LEFT JOIN sys.query_context_settings AS qcs
          ON qcs.context_settings_id = qsq.context_settings_id
        WHERE qsq.object_id = OBJECT_ID(N'dbo.tr_Posts_Audit_No_Safeties', N'TR')
    ) AS latest_stats
    WHERE latest_stats.row_number = 1;
END;
GO

🔥 Note: The set_options_decoded column decodes the set_options bitmask into human-readable SET option values. The bitmask decoding logic uses standard SQL Server SET option bitmask values documented in Microsoft's documentation for sys.dm_exec_plan_attributes and sys.query_context_settings. For more information on how SET options affect query performance and execution plans, see Erland Sommarskog's article "Slow in the Application, Fast in SSMS?".

The sp_CaptureQueryStoreHistory procedure queries Query Store for all queries related to the trigger and saves detailed information to dbo.QueryStoreHistory with a step description. It captures all rows from Query Store without grouping, including:

Query ID, query text ID, plan ID, and runtime stats ID
Context settings ID and decoded SET options (including IMPLICIT_TRANSACTIONS status)
Query SQL text
Runtime statistics (execution count, CPU time, duration, logical I/O reads, last execution time)

The test procedure calls this helper after each step, building a detailed history that maps Query Store data to specific operations. Analyze and aggregate this data later as needed.

Setup: Create Test Data Procedure

Now let’s create a stored procedure that fires the trigger in various ways. The procedure also includes a MERGE statement and dynamic SQL with sp_executesql (inspired by Paul White’s answer on StackOverflow) to demonstrate additional trigger firing scenarios:

CREATE OR ALTER PROCEDURE
    dbo.sp_TestTriggerFires
AS
BEGIN
    /* Set consistent options for Query Store tracking */
    SET NOCOUNT ON;
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    SET ARITHABORT ON;
    SET NUMERIC_ROUNDABORT OFF;
    SET IMPLICIT_TRANSACTIONS OFF;

    /* Store the IDs for later updates and deletes */
    DECLARE
        @inserted_ids TABLE
    (
        Id integer NOT NULL PRIMARY KEY,
        PostTypeId integer NOT NULL
    );

    DECLARE
        @question_ids TABLE
    (
        Id integer NOT NULL PRIMARY KEY
    );

    DECLARE
        @answer_ids TABLE
    (
        Id integer NOT NULL PRIMARY KEY
    );

    /* <strong>Step A:</strong> Insert 10 rows: 5 questions (PostTypeId=1), 5 answers (PostTypeId=2) */
    /* Use OUTPUT clause to capture IDs immediately - no table scan needed */
    INSERT INTO
        dbo.Posts
    (
        AcceptedAnswerId, AnswerCount, Body, CommentCount, CreationDate,
        FavoriteCount, LastActivityDate, LastEditDate, OwnerUserId, ParentId,
        PostTypeId, Score, Tags, Title, ViewCount
    )
    OUTPUT
        inserted.Id, inserted.PostTypeId
    INTO
        @inserted_ids (Id, PostTypeId)
    VALUES
        (NULL, 0, N'Test question body for trigger testing', 0, GETDATE(), 0, GETDATE(), NULL, 1, NULL, 1, 0, N'<test>', N'Test Question 1', 0),
        (NULL, 0, N'Test question body 2', 0, GETDATE(), 0, GETDATE(), NULL, 2, NULL, 1, 0, N'<test>', N'Test Question 2', 0),
        (NULL, 0, N'Test question body 3', 0, GETDATE(), 0, GETDATE(), NULL, 3, NULL, 1, 0, N'<test>', N'Test Question 3', 0),
        (NULL, 0, N'Test question body 4', 0, GETDATE(), 0, GETDATE(), NULL, 4, NULL, 1, 0, N'<test>', N'Test Question 4', 0),
        (NULL, 0, N'Test question body 5', 0, GETDATE(), 0, GETDATE(), NULL, 5, NULL, 1, 0, N'<test>', N'Test Question 5', 0),
        (NULL, 0, N'Test answer body 1', 0, GETDATE(), 0, GETDATE(), NULL, 6, 1, 2, 0, NULL, NULL, 0),
        (NULL, 0, N'Test answer body 2', 0, GETDATE(), 0, GETDATE(), NULL, 7, 1, 2, 0, NULL, NULL, 0),
        (NULL, 0, N'Test answer body 3', 0, GETDATE(), 0, GETDATE(), NULL, 8, 1, 2, 0, NULL, NULL, 0),
        (NULL, 0, N'Test answer body 4', 0, GETDATE(), 0, GETDATE(), NULL, 9, 1, 2, 0, NULL, NULL, 0),
        (NULL, 0, N'Test answer body 5', 0, GETDATE(), 0, GETDATE(), NULL, 10, 1, 2, 0, NULL, NULL, 0);

    /* Split inserted IDs into question and answer tables based on PostTypeId */
    INSERT INTO
        @question_ids
    (
        Id
    )
    SELECT
        ii.Id
    FROM @inserted_ids AS ii
    WHERE ii.PostTypeId = 1;

    INSERT INTO
        @answer_ids
    (
        Id
    )
    SELECT
        ii.Id
    FROM @inserted_ids AS ii
    WHERE ii.PostTypeId = 2;

    /* Capture Query Store after Step A */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 1,
        @step_description = N'<strong>Step A:</strong> INSERT 10 rows';

    /* Update all 10 rows with various changes */
    /* Question 1: Update Title */
    UPDATE
        p
    SET
        p.Title = N'Updated Test Question 1'
    FROM dbo.Posts AS p
    JOIN @question_ids AS q
      ON q.Id = p.Id
    WHERE p.Id =
    (
        SELECT TOP (1)
            q2.Id
        FROM @question_ids AS q2
        ORDER BY
            q2.Id
    );

    /* Capture Query Store after Step B */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 2,
        @step_description = N'<strong>Step B:</strong> UPDATE Question 1 (Title)';

    /* Question 2: Update CommentCount (should NOT trigger audit) */
    UPDATE
        p
    SET
        p.CommentCount = 5
    FROM dbo.Posts AS p
    JOIN @question_ids AS q
      ON q.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            q2.Id
        FROM @question_ids AS q2
        ORDER BY
            q2.Id
        OFFSET 1 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step C */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 3,
        @step_description = N'<strong>Step C:</strong> UPDATE Question 2 (CommentCount)';

    /* Question 3: Update Body (should NOT trigger audit) */
    UPDATE
        p
    SET
        p.Body = N'Updated body text'
    FROM dbo.Posts AS p
    JOIN @question_ids AS q
      ON q.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            q2.Id
        FROM @question_ids AS q2
        ORDER BY
            q2.Id
        OFFSET 2 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step D */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 4,
        @step_description = N'<strong>Step D:</strong> UPDATE Question 3 (Body)';

    /* Question 4: Update LastEditDate (should NOT trigger audit) */
    UPDATE
        p
    SET
        p.LastEditDate = GETDATE()
    FROM dbo.Posts AS p
    JOIN @question_ids AS q
      ON q.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            q2.Id
        FROM @question_ids AS q2
        ORDER BY
            q2.Id
        OFFSET 3 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step E */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 5,
        @step_description = N'<strong>Step E:</strong> UPDATE Question 4 (LastEditDate)';

    /* Question 5: Update Score */
    UPDATE
        p
    SET
        p.Score = 10
    FROM dbo.Posts AS p
    JOIN @question_ids AS q
      ON q.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            q2.Id
        FROM @question_ids AS q2
        ORDER BY
            q2.Id
        OFFSET 4 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step F */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 6,
        @step_description = N'<strong>Step F:</strong> UPDATE Question 5 (Score)';

    /* Answer 1: Update OwnerUserId */
    UPDATE
        p
    SET
        p.OwnerUserId = 100
    FROM dbo.Posts AS p
    JOIN @answer_ids AS a
      ON a.Id = p.Id
    WHERE p.Id =
    (
        SELECT TOP (1)
            a2.Id
        FROM @answer_ids AS a2
        ORDER BY
            a2.Id
    );

    /* Capture Query Store after Step G */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 7,
        @step_description = N'<strong>Step G:</strong> UPDATE Answer 1 (OwnerUserId)';

    /* Answer 2: Update CommentCount (should NOT trigger audit) */
    UPDATE
        p
    SET
        p.CommentCount = 3
    FROM dbo.Posts AS p
    JOIN @answer_ids AS a
      ON a.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            a2.Id
        FROM @answer_ids AS a2
        ORDER BY
            a2.Id
        OFFSET 1 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step H */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 8,
        @step_description = N'<strong>Step H:</strong> UPDATE Answer 2 (CommentCount)';

    /* Answer 3: Update Body (should NOT trigger audit) */
    UPDATE
        p
    SET
        p.Body = N'Updated answer body'
    FROM dbo.Posts AS p
    JOIN @answer_ids AS a
      ON a.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            a2.Id
        FROM @answer_ids AS a2
        ORDER BY
            a2.Id
        OFFSET 2 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step I */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 9,
        @step_description = N'<strong>Step I:</strong> UPDATE Answer 3 (Body)';

    /* Answer 4: Update LastEditDate (should NOT trigger audit) */
    UPDATE
        p
    SET
        p.LastEditDate = GETDATE()
    FROM dbo.Posts AS p
    JOIN @answer_ids AS a
      ON a.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            a2.Id
        FROM @answer_ids AS a2
        ORDER BY
            a2.Id
        OFFSET 3 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step J */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 10,
        @step_description = N'<strong>Step J:</strong> UPDATE Answer 4 (LastEditDate)';

    /* Answer 5: Update Score */
    UPDATE
        p
    SET
        p.Score = 5
    FROM dbo.Posts AS p
    JOIN @answer_ids AS a
      ON a.Id = p.Id
    WHERE p.Id =
    (
        SELECT
            a2.Id
        FROM @answer_ids AS a2
        ORDER BY
            a2.Id
        OFFSET 4 ROWS
        FETCH NEXT 1 ROW ONLY
    );

    /* Capture Query Store after Step K */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 11,
        @step_description = N'<strong>Step K:</strong> UPDATE Answer 5 (Score)';

    /* Delete all 10 rows */
    DELETE
        p
    FROM dbo.Posts AS p
    WHERE p.Id IN
    (
        SELECT
            q.Id
        FROM @question_ids AS q

        UNION ALL

        SELECT
            a.Id
        FROM @answer_ids AS a
    );

    /* Capture Query Store after Step L */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 12,
        @step_description = N'<strong>Step L:</strong> DELETE 10 rows';

    /* MERGE statement */
    /* If a matching row exists, update it; otherwise insert a new row */
    /* This fires the trigger on Posts, which writes to AuditPosts */
    DECLARE
        @merge_matched_count integer = 0,
        @merge_not_matched_count integer = 0;

    CREATE TABLE
        #merge_output
    (
        action nvarchar(10) NOT NULL,
        id integer NOT NULL
    );

    MERGE
        dbo.Posts AS target
    USING
    (
        SELECT TOP (1)
            Id = p.Id,
            Title = N'MERGE: Where Reality Never Lives Up To Expectations'
        FROM dbo.Posts AS p
        WHERE p.CreationDate >= DATEADD(MINUTE, -1, GETDATE())
        ORDER BY
            p.CreationDate DESC
        
        UNION ALL
        
        SELECT
            Id = -1,
            Title = N'MERGE: Where Reality Never Lives Up To Expectations'
        WHERE NOT EXISTS
        (
            SELECT
                1/0
            FROM dbo.Posts AS p2
            WHERE p2.CreationDate >= DATEADD(MINUTE, -1, GETDATE())
        )
    ) AS source
      ON target.Id = source.Id
    WHEN MATCHED
    THEN
        UPDATE
        SET
            target.LastActivityDate = GETDATE()
    WHEN NOT MATCHED BY TARGET
    THEN
        INSERT
        (
            AcceptedAnswerId, AnswerCount, Body, CommentCount, CreationDate,
            FavoriteCount, LastActivityDate, LastEditDate, OwnerUserId, ParentId,
            PostTypeId, Score, Tags, Title, ViewCount
        )
        VALUES
        (
            NULL, 0, N'Test row inserted via MERGE', 0, GETDATE(), 0, GETDATE(), NULL, 1, NULL, 1, 0, N'<test>', N'Merged Row', 0
        )
    OUTPUT
        $action,
        inserted.Id
    INTO
        #merge_output (action, id);

    SELECT
        @merge_matched_count = COUNT_BIG(*)
    FROM #merge_output
    WHERE action = N'UPDATE';

    SELECT
        @merge_not_matched_count = COUNT_BIG(*)
    FROM #merge_output
    WHERE action = N'INSERT';

    PRINT N'Step M (MERGE): ' + 
          CONVERT(nvarchar(10), @merge_matched_count) + N' row(s) updated (WHEN MATCHED), ' + 
          CONVERT(nvarchar(10), @merge_not_matched_count) + N' row(s) inserted (WHEN NOT MATCHED BY TARGET).';

    /* Capture Query Store after Step M */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 13,
        @step_description = N'<strong>Step M:</strong> MERGE statement';

    /* Dynamic SQL with sp_executesql */
    /* This fires the trigger on Posts via DELETE, which writes to AuditPosts */
    /* Delete the row that Step M (MERGE statement) modified - MERGE always creates or updates a row */
    /* Find the row with the most recent LastActivityDate that was updated in the last minute */
    DECLARE
        @sql nvarchar(max) = N'',
        @posts_id integer = 0;

    SELECT
        @posts_id = MAX(p.Id)
    FROM dbo.Posts AS p
    WHERE p.LastActivityDate >= DATEADD(MINUTE, -1, GETDATE())
    AND p.LastActivityDate = 
    (
        SELECT
            MAX(p2.LastActivityDate)
        FROM dbo.Posts AS p2
        WHERE p2.LastActivityDate >= DATEADD(MINUTE, -1, GETDATE())
    );

    IF @posts_id IS NULL OR @posts_id <= 0
    BEGIN
        RAISERROR(N'Dynamic SQL DELETE (Step N) did not find a row to delete. Expected at least one row with LastActivityDate updated by Step M (MERGE statement) in the last minute.', 16, 1);
        RETURN;
    END;

    SET @sql = N'
        DELETE /* run via dynamic sql */
            p
        FROM dbo.Posts AS p
        WHERE p.Id = @posts_id;
    ';

    EXECUTE sys.sp_executesql
        @sql,
        N'@posts_id integer',
        @posts_id;

    /* Capture Query Store after Step N */
    EXECUTE dbo.sp_CaptureQueryStoreHistory 
        @step_number = 14,
        @step_description = N'<strong>Step N:</strong> Dynamic SQL DELETE';
END;
GO

The test procedure performs the following operations in order:

Step	Operation	Details	Notes
A	INSERT 10 rows	Single INSERT statement with VALUES clause inserting 5 questions (PostTypeId=1) and 5 answers (PostTypeId=2)	Updates LastActivityDate (10 times), adds 4 rows to AuditPosts per row (Action=‘I’)
B	UPDATE Question 1	Updates Title column	Updates LastActivityDate, adds 4 rows to AuditPosts (Action=‘U’ - Title is monitored)
C	UPDATE Question 2	Updates CommentCount column	Updates LastActivityDate only (CommentCount not monitored)
D	UPDATE Question 3	Updates Body column	Updates LastActivityDate only (Body not monitored)
E	UPDATE Question 4	Updates LastEditDate column	Updates LastActivityDate only (LastEditDate not monitored)
F	UPDATE Question 5	Updates Score column	Updates LastActivityDate, adds 4 rows to AuditPosts (Action=‘U’ - Score is monitored)
G	UPDATE Answer 1	Updates OwnerUserId column	Updates LastActivityDate, adds 4 rows to AuditPosts (Action=‘U’ - OwnerUserId is monitored)
H	UPDATE Answer 2	Updates CommentCount column	Updates LastActivityDate only (CommentCount not monitored)
I	UPDATE Answer 3	Updates Body column	Updates LastActivityDate only (Body not monitored)
J	UPDATE Answer 4	Updates LastEditDate column	Updates LastActivityDate only (LastEditDate not monitored)
K	UPDATE Answer 5	Updates Score column	Updates LastActivityDate, adds 4 rows to AuditPosts (Action=‘U’ - Score is monitored)
L	DELETE 10 rows	Single DELETE statement removing all 10 inserted rows	Adds 4 rows to AuditPosts per row (Action=‘D’), does not update LastActivityDate
M	MERGE statement	MERGE operation that updates LastActivityDate if row exists, or inserts new row if not	Updates LastActivityDate, adds 4 rows to AuditPosts (Action=‘I’ in this case - inserts new row)
N	Dynamic SQL DELETE	DELETE statement executed via `sp_executesql`	Adds 4 rows to AuditPosts (Action=‘D’), does not update LastActivityDate. Deletes row from Step M.

Summary:

Steps overview: A (INSERT 10 rows), B-K (10 individual UPDATEs), L (DELETE 10 rows), M (MERGE statement), N (Dynamic SQL DELETE)
Trigger fires: 14 total (1 INSERT statement, 10 UPDATE statements, 1 DELETE statement, 1 MERGE statement, 1 dynamic SQL DELETE statement). The trigger fires once per DML statement, not once per row.
LastActivityDate updates: 21 total rows updated (10 rows from step A INSERT, 10 rows from steps B-K UPDATEs, 1 row from step M MERGE)
AuditPosts inserts: 104 total rows (44 Action=‘I’ from steps A and M, 16 Action=‘U’ from steps B/F/G/K, 44 Action=‘D’ from steps L and N)
Operations that update LastActivityDate: All INSERT and UPDATE operations (steps A-K, M)
Operations that don’t update LastActivityDate: All DELETE operations (steps L, N) because DELETE operations don’t have an inserted table

Run Test Procedure and Review Audit Table Rows Inserted by Trigger

Let’s run the procedure and check the audit table:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
GO

EXECUTE dbo.sp_TestTriggerFires;
GO

/* Review audit results */
SELECT
    ap.Action,
    audit_count = COUNT_BIG(*),
    posts_affected = COUNT_BIG(DISTINCT ap.PostsId)
FROM dbo.AuditPosts AS ap
GROUP BY
    ap.Action
ORDER BY
    ap.Action;
GO

/* Detailed view */
SELECT
    ap.Action,
    ap.PostsId,
    ap.ColumnName,
    ap.ColumnValue,
    ap.ActivityDate
FROM dbo.AuditPosts AS ap
ORDER BY
    ap.PostsId,
    ap.Action,
    ap.ColumnName;
GO

Each run of dbo.sp_TestTriggerFires should add 104 rows to dbo.AuditPosts:

44 rows with Action = ‘I’ (11 inserts × 4 columns each: 10 from Step A, 1 from Step M MERGE when it inserts)
16 rows with Action = ‘U’ (4 updates × 4 columns each: from Steps B, F, G, K that change monitored columns)
44 rows with Action = ‘D’ (11 deletes × 4 columns each: 10 from Step L, 1 from Step N dynamic SQL DELETE)

Review Query Store Data

Since we captured Query Store data after each step in the dbo.QueryStoreHistory table, we can analyze what changed between steps.

/* Summary: final state for each unique query_id and context_settings_id combination */
SELECT
    latest.query_id,
    latest.context_settings_id,
    /* Only show IMPLICIT_TRANSACTIONS since that's the key difference */
    implicit_transactions = 
        CASE 
            WHEN latest.set_options_decoded LIKE N'%IMPLICIT_TRANSACTIONS ON%' 
            THEN N'ON' 
            ELSE N'OFF' 
        END,
    /* Summarize query text to identify trigger operations */
    query_summary = 
        CASE 
            WHEN latest.query_sql_text LIKE N'%UPDATE%LastActivityDate%' 
            THEN N'UPDATE LastActivityDate'
            WHEN latest.query_sql_text LIKE N'%INSERT INTO%dbo.AuditPosts%' 
            AND latest.query_sql_text LIKE N'%Action = ''I''%' 
            THEN N'INSERT INTO AuditPosts (Action=I)'
            WHEN latest.query_sql_text LIKE N'%INSERT INTO%dbo.AuditPosts%' 
            AND latest.query_sql_text LIKE N'%Action = ''U''%' 
            THEN N'INSERT INTO AuditPosts (Action=U)'
            WHEN latest.query_sql_text LIKE N'%INSERT INTO%dbo.AuditPosts%' 
            AND latest.query_sql_text LIKE N'%Action = ''D''%' 
            THEN N'INSERT INTO AuditPosts (Action=D)'
            ELSE LEFT(latest.query_sql_text, 80) + N'...'
        END,
    /* List only steps where execution count increased, in order, with execution counts */
    steps_used = 
        STRING_AGG
        (
            step_list.step_description + 
            N' (' + 
            CONVERT(nvarchar(10), step_list.executions_added) + 
            CASE 
                WHEN step_list.executions_added = 1 
                THEN N' exec' 
                ELSE N' execs' 
            END + 
            N')',
            N', '
        )
        WITHIN GROUP
        (
            ORDER BY
                step_list.step_number ASC
        ),
    /* Sum execution counts across all plans for the latest step */
    total_executions = 
        (
            SELECT
                SUM(qsh_final.count_executions)
            FROM dbo.QueryStoreHistory AS qsh_final
            WHERE qsh_final.query_id = latest.query_id
            AND   qsh_final.context_settings_id = latest.context_settings_id
            AND   qsh_final.step_number = latest.max_step_number
        ),
    total_compiles = latest.max_count_compiles
FROM
(
    /* Get latest state for each query_id and context_settings_id */
    SELECT
        qsh.query_id,
        qsh.context_settings_id,
        qsh.set_options_decoded,
        qsh.query_sql_text,
        max_step_number = MAX(qsh.step_number),
        max_count_compiles = MAX(qsh.count_compiles),
        row_number = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
                    qsh.query_id,
                    qsh.context_settings_id
                ORDER BY
                    MAX(qsh.step_number) DESC
            )
    FROM dbo.QueryStoreHistory AS qsh
    GROUP BY
        qsh.query_id,
        qsh.context_settings_id,
        qsh.set_options_decoded,
        qsh.query_sql_text
) AS latest
/* Get only steps where execution count increased - aggregate by step_number first, summing across all plans */
CROSS APPLY
(
    SELECT
        step_agg.step_number,
        step_agg.step_description,
        step_agg.total_count_executions,
        prev_step.total_count_executions AS prev_count_executions,
        /* Calculate executions added in this step */
        executions_added = 
            CASE 
                WHEN prev_step.total_count_executions IS NULL 
                THEN step_agg.total_count_executions
                ELSE step_agg.total_count_executions - prev_step.total_count_executions
            END
    FROM
    (
        /* Aggregate by step_number, summing execution counts across all plans */
        SELECT
            qsh2.step_number,
            qsh2.step_description,
            total_count_executions = SUM(qsh2.count_executions)
        FROM dbo.QueryStoreHistory AS qsh2
        WHERE qsh2.query_id = latest.query_id
        AND   qsh2.context_settings_id = latest.context_settings_id
        GROUP BY
            qsh2.step_number,
            qsh2.step_description
    ) AS step_agg
    /* Get previous step's total execution count (summed across all plans) */
    /* Get the immediately previous step (highest step_number < current step) */
    OUTER APPLY
    (
        SELECT
            total_count_executions = SUM(qsh3.count_executions)
        FROM dbo.QueryStoreHistory AS qsh3
        WHERE qsh3.query_id = latest.query_id
        AND   qsh3.context_settings_id = latest.context_settings_id
        AND   qsh3.step_number = 
            (
                SELECT
                    MAX(qsh_prev.step_number)
                FROM dbo.QueryStoreHistory AS qsh_prev
                WHERE qsh_prev.query_id = latest.query_id
                AND   qsh_prev.context_settings_id = latest.context_settings_id
                AND   qsh_prev.step_number < step_agg.step_number
            )
    ) AS prev_step
    /* Only include steps where execution count increased (or first appearance) */
    WHERE (
              prev_step.total_count_executions IS NULL
              OR step_agg.total_count_executions > prev_step.total_count_executions
          )
) AS step_list
WHERE latest.row_number = 1
GROUP BY
    latest.query_id,
    latest.context_settings_id,
    latest.set_options_decoded,
    latest.query_sql_text,
    latest.max_step_number,
    latest.max_count_compiles
ORDER BY
    latest.query_id,
    latest.context_settings_id;
GO

SELECT
    qsq.query_id,
    qsq.context_settings_id,
    total_executions = SUM(qsrs.count_executions),
    total_compiles = MAX(qsq.count_compiles)
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
  ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsrs
  ON qsrs.plan_id = qsp.plan_id
WHERE qsq.object_id = OBJECT_ID(N'dbo.tr_Posts_Audit_No_Safeties', N'TR')
GROUP BY
    qsq.query_id,
    qsq.context_settings_id
ORDER BY
    qsq.query_id,
    qsq.context_settings_id;
GO

This summary shows:

Only steps where execution count increased for each query_id (steps_used), listed in order
Final execution and compile counts

Here’s the actual Query Store output from running the test procedure. Look for how query IDs are split by context settings and which steps use each query ID:

query id (context settings id)	implicit transaction?	query summary	steps used	total executions
3 (2)	OFF	UPDATE LastActivityDate	Step A: INSERT 10 rows (1 exec), Step L: DELETE 10 rows (1 exec)	2
4 (2)	OFF	INSERT INTO AuditPosts (Action=I)	Step A: INSERT 10 rows (1 exec), Step L: DELETE 10 rows (1 exec)	2
5 (2)	OFF	INSERT INTO AuditPosts (Action=U)	Step A: INSERT 10 rows (1 exec), Step L: DELETE 10 rows (1 exec)	2
6 (2)	OFF	INSERT INTO AuditPosts (Action=D)	Step A: INSERT 10 rows (1 exec), Step L: DELETE 10 rows (1 exec)	2
12 (7)	ON	UPDATE LastActivityDate	Step B: UPDATE Question 1 (Title) (1 exec), Step C: UPDATE Question 2 (CommentCount) (1 exec), Step D: UPDATE Question 3 (Body) (1 exec), Step E: UPDATE Question 4 (LastEditDate) (1 exec), Step F: UPDATE Question 5 (Score) (1 exec), Step G: UPDATE Answer 1 (OwnerUserId) (1 exec), Step H: UPDATE Answer 2 (CommentCount) (1 exec), Step I: UPDATE Answer 3 (Body) (1 exec), Step J: UPDATE Answer 4 (LastEditDate) (1 exec), Step K: UPDATE Answer 5 (Score) (1 exec), Step N: Dynamic SQL DELETE (1 exec)	11
13 (7)	ON	INSERT INTO AuditPosts (Action=I)	Step B: UPDATE Question 1 (Title) (1 exec), Step C: UPDATE Question 2 (CommentCount) (1 exec), Step D: UPDATE Question 3 (Body) (1 exec), Step E: UPDATE Question 4 (LastEditDate) (1 exec), Step F: UPDATE Question 5 (Score) (1 exec), Step G: UPDATE Answer 1 (OwnerUserId) (1 exec), Step H: UPDATE Answer 2 (CommentCount) (1 exec), Step I: UPDATE Answer 3 (Body) (1 exec), Step J: UPDATE Answer 4 (LastEditDate) (1 exec), Step K: UPDATE Answer 5 (Score) (1 exec), Step N: Dynamic SQL DELETE (1 exec)	11
14 (7)	ON	INSERT INTO AuditPosts (Action=U)	Step B: UPDATE Question 1 (Title) (1 exec), Step C: UPDATE Question 2 (CommentCount) (1 exec), Step D: UPDATE Question 3 (Body) (1 exec), Step E: UPDATE Question 4 (LastEditDate) (1 exec), Step F: UPDATE Question 5 (Score) (1 exec), Step G: UPDATE Answer 1 (OwnerUserId) (1 exec), Step H: UPDATE Answer 2 (CommentCount) (1 exec), Step I: UPDATE Answer 3 (Body) (1 exec), Step J: UPDATE Answer 4 (LastEditDate) (1 exec), Step K: UPDATE Answer 5 (Score) (1 exec), Step N: Dynamic SQL DELETE (1 exec)	11
15 (7)	ON	INSERT INTO AuditPosts (Action=D)	Step B: UPDATE Question 1 (Title) (1 exec), Step C: UPDATE Question 2 (CommentCount) (1 exec), Step D: UPDATE Question 3 (Body) (1 exec), Step E: UPDATE Question 4 (LastEditDate) (1 exec), Step F: UPDATE Question 5 (Score) (1 exec), Step G: UPDATE Answer 1 (OwnerUserId) (1 exec), Step H: UPDATE Answer 2 (CommentCount) (1 exec), Step I: UPDATE Answer 3 (Body) (1 exec), Step J: UPDATE Answer 4 (LastEditDate) (1 exec), Step K: UPDATE Answer 5 (Score) (1 exec), Step N: Dynamic SQL DELETE (1 exec)	11
27 (10)	ON	UPDATE LastActivityDate	Step M: MERGE statement (1 exec)	1
28 (10)	ON	INSERT INTO AuditPosts (Action=I)	Step M: MERGE statement (1 exec)	1
29 (10)	ON	INSERT INTO AuditPosts (Action=U)	Step M: MERGE statement (1 exec)	1
30 (10)	ON	INSERT INTO AuditPosts (Action=D)	Step M: MERGE statement (1 exec)	1
31 (11)	ON	UPDATE LastActivityDate	Step M: MERGE statement (1 exec)	1
32 (11)	ON	INSERT INTO AuditPosts (Action=I)	Step M: MERGE statement (1 exec)	1
33 (11)	ON	INSERT INTO AuditPosts (Action=U)	Step M: MERGE statement (1 exec)	1
34 (11)	ON	INSERT INTO AuditPosts (Action=D)	Step M: MERGE statement (1 exec)	1

Observations from the Query Store data:

Separate query_ids for different trigger operations: The same DML statement (e.g., INSERT 10 rows in Step A) appears across multiple query_ids (3, 4, 5, 6), each representing a different trigger operation type:
- Query ID 3: UPDATE LastActivityDate
- Query ID 4: INSERT INTO AuditPosts with Action=‘I’
- Query ID 5: INSERT INTO AuditPosts with Action=‘U’
- Query ID 6: INSERT INTO AuditPosts with Action=‘D’
This matches the trigger’s structure: it contains separate SQL statements for each operation type (one UPDATE for LastActivityDate, and separate INSERT statements for Action=‘I’, ‘U’, and ‘D’). Because the trigger uses no safety checks (no IF EXISTS blocks), all these statements are always present and execute based on their WHERE clause conditions (WHERE NOT EXISTS (SELECT 1/0 FROM deleted AS d) for INSERT operations, ISNULL comparisons for UPDATE operations, LEFT JOIN inserted ... WHERE i.Id IS NULL for DELETE operations), rather than being conditionally executed via IF statements.
Important note about Query Store execution counts: Query Store counts all statement executions in the trigger, even when WHERE clauses match 0 rows. For example, Query ID 3 (UPDATE LastActivityDate) shows execution during Step L DELETE, but this UPDATE affects 0 rows because it joins to the inserted table which is empty during DELETE operations. Query Store tracks the execution, but the statement does no actual work. This is why the test summary correctly notes that DELETE operations don’t update LastActivityDate, even though Query Store shows executions.
Multiple context settings created: Even though we didn’t vary user settings, Query Store created 4 different context_settings_id values:
- context_settings_id = 2 (IMPLICIT_TRANSACTIONS OFF): Used for Step A (INSERT 10 rows) and Step L (DELETE 10 rows)
- context_settings_id = 7 (IMPLICIT_TRANSACTIONS ON): Used for Steps B-K and N (individual UPDATEs and dynamic SQL DELETE)
- context_settings_id = 10 (IMPLICIT_TRANSACTIONS ON): Used for Step M (MERGE statement)
- context_settings_id = 11 (IMPLICIT_TRANSACTIONS ON): Also used for Step M (MERGE statement)
Interestingly, the MERGE statement (Step M) appears with two different context_settings_id values (10 and 11), both showing IMPLICIT_TRANSACTIONS ON. This happens even though we didn’t change SET options between operations. The MERGE fires the trigger twice (once for the UPDATE action, once for the INSERT action), and Query Store assigns different context_settings_id values to each trigger execution from that single MERGE statement.

The same DML statement creates multiple query_ids (one per trigger operation) and multiple context_settings_id values, even when you don’t change any settings. Data ends up spread across all these query_ids and context settings, so it’s easy to miss executions if your queries don’t aggregate across everything.

XEvents Event File Session on sqlserver.module_start

Let’s create an Extended Events session to capture when the trigger module starts. This approach is based on Tomáš Zíka’s answer from straightforwardsql.com, using sqlserver.module_start.

This is created with MAX_DISPATCH_LATENCY = 5 SECONDS for convenience in testing, for production code you generally want to use a longer amount.

/* Drop if exists and create idempotent session */
IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_module_start'
)
BEGIN
    ALTER EVENT SESSION [trigger_module_start]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_module_start]
    ON SERVER;
END;
GO

CREATE EVENT SESSION [trigger_module_start]
ON SERVER
    ADD EVENT sqlserver.module_start
    (
        ACTION
        (
            sqlserver.sql_text,
            sqlserver.tsql_stack,
            sqlserver.client_app_name,
            sqlserver.database_name,
            sqlserver.session_id,
            sqlserver.username
        )
        WHERE
        (
            [sqlserver].[database_name] = N'StackOverflow2013'
            AND [object_name] = N'tr_Posts_Audit_No_Safeties'
        )
    )
    ADD TARGET package0.event_file
    (
        SET filename = N'C:\Temp\trigger_module_start.xel',
            max_file_size = 50,
            max_rollover_files = 5
    )
WITH
(
    MAX_MEMORY = 4096 KB,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
);
GO

ALTER EVENT SESSION [trigger_module_start]
ON SERVER
STATE = START;
GO

XEvents Event File Session on sqlserver.module_end

Let’s also create an Extended Events session to capture when the trigger module ends. Unless you are concerned about lookign for trigger failures, this is arguably more useful than module_start, as it allows us to capture duration and row_count metrics.

This is created with MAX_DISPATCH_LATENCY = 5 SECONDS for convenience in testing, for production code you generally want to use a longer amount.

/* Drop if exists and create idempotent session */
IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_module_end'
)
BEGIN
    ALTER EVENT SESSION [trigger_module_end]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_module_end]
    ON SERVER;
END;
GO

CREATE EVENT SESSION [trigger_module_end]
ON SERVER
    ADD EVENT sqlserver.module_end
    (
        ACTION
        (
            sqlserver.sql_text,
            sqlserver.tsql_stack,
            sqlserver.client_app_name,
            sqlserver.database_name,
            sqlserver.session_id,
            sqlserver.username
        )
        WHERE
        (
            [sqlserver].[database_name] = N'StackOverflow2013'
            AND [object_name] = N'tr_Posts_Audit_No_Safeties'
        )
    )
    ADD TARGET package0.event_file
    (
        SET filename = N'C:\Temp\trigger_module_end.xel',
            max_file_size = 50,
            max_rollover_files = 5
    )
WITH
(
    MAX_MEMORY = 4096 KB,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
);
GO

ALTER EVENT SESSION [trigger_module_end]
ON SERVER
STATE = START;
GO

XEvents Event File Session on Stored Procedure Statement Completions

Now let’s create a session to capture statement completions within the trigger. We’ll capture sp_statement_completed filtered to only the trigger object to see which statements execute inside the trigger.

Note: To filter sp_statement_completed events by object name, you must include SET collect_object_name = (1) in the event definition. When efficiency is important, filter on object_id instead of object_name (object_id is always available and doesn’t require the collect_object_name setting).

This is created with MAX_DISPATCH_LATENCY = 5 SECONDS for convenience in testing, for production code you generally want to use a longer amount.

IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_completions'
)
BEGIN
    ALTER EVENT SESSION [trigger_completions]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_completions]
    ON SERVER;
END;
GO

CREATE EVENT SESSION [trigger_completions]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (
        SET collect_object_name = (1)
        ACTION
        (
            sqlserver.client_app_name,
            sqlserver.database_name,
            sqlserver.username,
            sqlserver.session_id,
            sqlserver.sql_text
        )
        WHERE
        (
            [sqlserver].[database_name] = N'StackOverflow2013'
            AND [object_name] = N'tr_Posts_Audit_No_Safeties'
        )
    )
    ADD TARGET package0.event_file
    (
        SET filename = N'C:\Temp\trigger_completions.xel',
            max_file_size = 50,
            max_rollover_files = 5
    )
WITH
(
    MAX_MEMORY = 4096 KB,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
);
GO

ALTER EVENT SESSION [trigger_completions]
ON SERVER
STATE = START;
GO

XEvents Histogram Session on sp_statement_completed

Now let’s create a histogram session that tracks statement executions within the trigger. This histogram counts executions per unique statement, giving us an aggregated view of which statements execute most frequently.

This uses a synchronous target (histogram) rather than an asynchronous target (event_file), so it aggregates data in memory rather than writing individual events to disk.

IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_statement_histogram'
)
BEGIN
    ALTER EVENT SESSION [trigger_statement_histogram]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_statement_histogram]
    ON SERVER;
END;
GO

CREATE EVENT SESSION [trigger_statement_histogram]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (
        SET collect_object_name = (1)
        WHERE
        (
            [sqlserver].[database_name] = N'StackOverflow2013'
            AND [object_name] = N'tr_Posts_Audit_No_Safeties'
        )
    )
    ADD TARGET package0.histogram
    (
        SET filtering_event_name = N'sqlserver.sp_statement_completed',
            slots = 256,
            source = N'object_name',
            source_type = 0  
    )
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
);
GO

ALTER EVENT SESSION [trigger_statement_histogram]
ON SERVER
STATE = START;
GO

Run our Test Procedure Again, Then Query All Trace Files

Now let’s run our test procedure again and query the Extended Events data.

Because the Extended Events session is configured with MAX_DISPATCH_LATENCY = 5 SECONDS, we wait 5 seconds after running the test procedure before querying the trace files.

Run Test Procedure

/* Run test - audit table will accumulate rows from all runs */
/* Ensure consistent SET options for Query Store tracking */
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
GO

EXECUTE dbo.sp_TestTriggerFires;
GO

Because the Extended Events session is configured with MAX_DISPATCH_LATENCY = 5 SECONDS, we need to wait at least 5 seconds after running the test procedure before querying the trace files. This ensures events have been flushed from the buffer to disk.

Query module_start Events

SELECT
    event_data.value('(event/@name)[1]', 'nvarchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_timestamp,
    event_data.value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(128)') AS object_name,
    event_data.value('(event/data[@name="object_id"]/value)[1]', 'integer') AS object_id,
    calling_code = event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    tsql_stack = CONVERT(nvarchar(max), event_data.query('(event/action[@name="tsql_stack"]/value)')),
    client_app = event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)'),
    session_id = event_data.value('(event/action[@name="session_id"]/value)[1]', 'integer'),
    username = event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)'),
    full_event_xml = event_data
FROM
(
    SELECT
        CAST(event_data AS xml) AS event_data
    FROM sys.fn_xe_file_target_read_file
    (
        N'C:\Temp\trigger_module_start*.xel',
        NULL,
        NULL,
        NULL
    )
) AS x
ORDER BY
    event_data.value('(event/@timestamp)[1]', 'datetime2');
GO

🔥 Note: The query includes the tsql_stack action which contains the call stack with sql_handle values that can be used to identify the DML statement that fired the trigger. To parse the tsql_stack and retrieve statement text from the plan cache, see Tomáš Zíka's answer on Stack Overflow for detailed instructions.

Query module_end Events

Module_end events have duration and row_count columns, so those are included here. Duration is in microseconds on SQL Server 2025.

SELECT
    event_data.value('(event/@name)[1]', 'nvarchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_timestamp,
    event_data.value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(128)') AS object_name,
    event_data.value('(event/data[@name="object_id"]/value)[1]', 'integer') AS object_id,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'integer') AS duration,
    event_data.value('(event/data[@name="row_count"]/value)[1]', 'integer') AS row_count,
    calling_code = event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    tsql_stack = CONVERT(nvarchar(max), event_data.query('(event/action[@name="tsql_stack"]/value)')),
    client_app = event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)'),
    session_id = event_data.value('(event/action[@name="session_id"]/value)[1]', 'integer'),
    username = event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)'),
    full_event_xml = event_data
FROM
(
    SELECT
        CAST(event_data AS xml) AS event_data
    FROM sys.fn_xe_file_target_read_file
    (
        N'C:\Temp\trigger_module_end*.xel',
        NULL,
        NULL,
        NULL
    )
) AS x
ORDER BY
    event_data.value('(event/@timestamp)[1]', 'datetime2');
GO

Query Completion Events - sp_statement_Completed for Trigger Statements

SELECT
    event_data.value('(event/@name)[1]', 'nvarchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_microseconds,
    event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time_microseconds,
    event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
    event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS statement_text,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
    object_name = event_data.value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(128)'),
    object_id = event_data.value('(event/data[@name="object_id"]/value)[1]', 'integer'),
    client_app = event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)'),
    session_id = event_data.value('(event/action[@name="session_id"]/value)[1]', 'integer'),
    username = event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)'),
    full_event_xml = event_data
FROM
(
    SELECT
        CAST(event_data AS xml) AS event_data
    FROM sys.fn_xe_file_target_read_file
    (
        N'C:\Temp\trigger_completions*.xel',
        NULL,
        NULL,
        NULL
    )
) AS x
ORDER BY
    event_timestamp;
GO

Query Aggregate sp_statement_completed Events

To get aggregated metrics like total CPU time and total logical reads per statement, query the event_file target from trigger_completions and aggregate.

SELECT
    statement_text,
    execution_count = COUNT_BIG(*),
    total_cpu_time_ms = SUM(cpu_time_microseconds) / 1000.0,
    avg_cpu_time_ms = AVG(cpu_time_microseconds) / 1000.0,
    total_logical_reads = SUM(logical_reads),
    avg_logical_reads = AVG(logical_reads),
    total_duration_ms = SUM(duration_microseconds) / 1000.0,
    avg_duration_ms = AVG(duration_microseconds) / 1000.0
FROM
(
    SELECT
        statement_text = event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)'),
        cpu_time_microseconds = event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint'),
        logical_reads = event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint'),
        duration_microseconds = event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint')
    FROM
    (
        SELECT
            CAST(event_data AS xml) AS event_data
        FROM sys.fn_xe_file_target_read_file
        (
            N'C:\Temp\trigger_completions*.xel',
            NULL,
            NULL,
            NULL
        )
    ) AS x
) AS statement_data
GROUP BY
    statement_text
ORDER BY
    total_cpu_time_ms DESC;
GO

Query histogram Target for Statement Execution Counts

SELECT
    slot_data.value('(value)[1]', 'nvarchar(max)') AS statement_text,
    slot_data.value('(@count)[1]', 'bigint') AS execution_count
FROM
(
    SELECT
        CAST(target_data AS xml) AS target_data
    FROM sys.dm_xe_session_targets AS xst
    JOIN sys.dm_xe_sessions AS xs
      ON xs.address = xst.event_session_address
    WHERE xs.name = N'trigger_statement_histogram'
    AND xst.target_name = N'histogram'
) AS histogram_data
CROSS APPLY target_data.nodes('//HistogramTarget/Slot') AS histogram_slots(slot_data)
ORDER BY
    slot_data.value('(@count)[1]', 'bigint') DESC;
GO

Extended Events Observations

Comparing Extended Events data with the audit table and Query Store:

Trigger execution counts:

Extended Events module_start and module_end captured 15 trigger executions, even though the test procedure executed only 14 DML statements. The discrepancy comes from MERGE: as Paul White explains, MERGE can fire triggers multiple times—once for each action type (INSERT, UPDATE, DELETE) that executes. Our MERGE statement (Step M) performs both an INSERT (WHEN NOT MATCHED BY TARGET) and an UPDATE (WHEN MATCHED), so the trigger fires twice for that single MERGE statement.

Query Store execution counts align with this when aggregated across all query_ids and context_settings_id values:

Query IDs 3-6 (context_settings_id 2): 2 executions total (Step A INSERT + Step L DELETE)
Query IDs 12-15 (context_settings_id 7): 11 executions total (Steps B-K individual UPDATEs + Step N dynamic SQL DELETE)
Query IDs 27-34 (context_settings_id 10, 11): 2 executions total (Step M MERGE fires trigger twice, creating two different context_settings_id values)
Total: 2 + 11 + 2 = 15 trigger executions

Statement-level vs query-level granularity:

Extended Events sp_statement_completed captured 75 individual statement executions within the trigger. This is statement-level detail—each SQL statement that executed inside the trigger, not the trigger executions themselves. Each trigger execution contains multiple statements (SET NOCOUNT ON, UPDATE LastActivityDate, INSERT INTO AuditPosts statements).

Query Store tracks query-level executions aggregated by query_id and context_settings_id. The same trigger operation (like “UPDATE LastActivityDate”) appears across multiple query_ids depending on context settings, requiring careful aggregation to get accurate counts.

The histogram target shows execution counts per unique statement, matching the event_file counts when grouped by statement text.

Row-level results:

The audit table shows 104 rows inserted—this is row-level detail, a different granularity entirely from both Extended Events statement counts and Query Store query counts.

Cleanup

This script tidies everything up:

/* Stop and drop Extended Events sessions */
IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_module_start'
)
BEGIN
    ALTER EVENT SESSION [trigger_module_start]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_module_start]
    ON SERVER;
END;
GO

IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_module_end'
)
BEGIN
    ALTER EVENT SESSION [trigger_module_end]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_module_end]
    ON SERVER;
END;
GO

IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_completions'
)
BEGIN
    ALTER EVENT SESSION [trigger_completions]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_completions]
    ON SERVER;
END;
GO

IF EXISTS
(
    SELECT
        1/0
    FROM sys.server_event_sessions AS ses
    WHERE ses.name = N'trigger_statement_histogram'
)
BEGIN
    ALTER EVENT SESSION [trigger_statement_histogram]
    ON SERVER
    STATE = STOP;

    DROP EVENT SESSION [trigger_statement_histogram]
    ON SERVER;
END;
GO

/* Clean up test objects */
IF OBJECT_ID(N'dbo.tr_Posts_Audit_No_Safeties', N'TR') IS NOT NULL
BEGIN
    DROP TRIGGER dbo.tr_Posts_Audit_No_Safeties;
END;
GO

IF OBJECT_ID(N'dbo.sp_TestTriggerFires', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_TestTriggerFires;
END;
GO

IF OBJECT_ID(N'dbo.sp_CaptureQueryStoreHistory', N'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_CaptureQueryStoreHistory;
END;
GO

IF OBJECT_ID(N'dbo.QueryStoreHistory', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.QueryStoreHistory;
END;
GO

IF OBJECT_ID(N'dbo.AuditPosts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.AuditPosts;
END;
GO

What We Observed About Tracing Trigger Executions

Both Query Store and Extended Events can trace trigger executions, but they work at different levels of detail and have different strengths.

Query Store tracks query-level executions, which means trigger code gets split across multiple query_ids (one per SQL statement in the trigger). The same DML statement can create multiple query_ids and context_settings_id values, even when you don’t change any session settings. You need to aggregate across all query_ids and context settings to get accurate execution counts. The upside is that Query Store automatically captures execution plans, runtime statistics, and performance metrics without any additional setup.

Extended Events gives you more control over what you capture. The sqlserver.module_start event captures each time the trigger module starts executing, giving you a clean count of trigger executions. The sp_statement_completed event captures individual statement executions within the trigger, showing you exactly which statements ran and their performance metrics. Extended Events requires more setup and configuration, but it provides statement-level detail that Query Store doesn’t expose directly.

To filter sp_statement_completed events by object name, you must include SET collect_object_name = (1) in the event definition. For better performance, you can filter on object_id instead of object_name (object_id is always available and doesn’t require the collect_object_name setting).

Key differences we found:

Execution counts: Extended Events module_start showed 15 trigger executions, which matched Query Store’s aggregated counts once we accounted for MERGE firing the trigger twice.
Granularity: Extended Events sp_statement_completed captured 75 individual statement executions within the trigger, while Query Store tracked query-level executions aggregated by query_id. The audit table showed 104 rows inserted, which is yet another level of detail (row-level results).
Data organization: Query Store data spreads across multiple query_ids and context_settings_id values, making it easy to miss executions if your queries don’t aggregate properly. Extended Events gives you event-level data that’s easier to filter and analyze, but requires more manual setup.

For tracing trigger executions, Extended Events provides cleaner, more direct counts and statement-level detail. Query Store works well for performance analysis of the queries within triggers, but requires careful aggregation to get accurate execution counts.

How to Trace Trigger Executions with Query Store and Extended Events

How to Trace Trigger Executions with Query Store and Extended Events

Setup: Enable and Clear Query Store

Setup: Create the Audit Table

Setup: Create the Trigger on Posts

Setup: Create Query Store History Capture Helper

Setup: Create Test Data Procedure

Run Test Procedure and Review Audit Table Rows Inserted by Trigger

Review Query Store Data

XEvents Event File Session on sqlserver.module_start

XEvents Event File Session on sqlserver.module_end

XEvents Event File Session on Stored Procedure Statement Completions

XEvents Histogram Session on sp_statement_completed

Run our Test Procedure Again, Then Query All Trace Files

Run Test Procedure

Query module_start Events

Query module_end Events

Query Completion Events - sp_statement_Completed for Trigger Statements

Query Aggregate sp_statement_completed Events

Query histogram Target for Statement Execution Counts

Extended Events Observations

Cleanup

What We Observed About Tracing Trigger Executions

RELATED POSTS

How to Filter RPC_Completed Events in Extended Events: SQL Server Guide

Erik Darling and Kendra Little Talk AI, Databases, and SQL Server 2025

Azure SQL Managed Instance Memory-to-Core Math Still Doesn't Work, Even in GPv2