Press "Enter" to skip to content

Category: Triggers

Tracing Trigger Executions in SQL Server

Published 2026-01-06 by Kevin Feasel

Kendra Little does some investigating:

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.

This is definitely worth the read. Kendra does a great job laying things out in a thoughtful manner and provides a lot of additional insight.

Leave a Comment

Business Rule Automation in SQL Server Triggers

Published 2025-08-18 by Kevin Feasel

Kenneth Omorodion uses triggers:

The advantage of using triggers is that the same processing can occur regardless of where or how the data has been inserted, updated or deleted. In this article, we look at several examples of where and why triggers could be useful along with an example use case.

I’m not a huge fan of using triggers for handling business rules for a few reasons. The biggest one is that triggers get lost in the mix too easily—it’s not easy to see that a trigger exists and that it is the thing modifying data. This is probably one of the better setups I’ve seen for triggers, as it does include an audit log table and the triggers assume multiple rows rather than expecting a single row will come in.

Comments closed

Building a Better Trigger

Published 2025-06-19 by Kevin Feasel

Steve Jones is done with mousetraps:

This seemed to work, but did it really?

The Problem

Let’s illustrate the big problem with this change. I’ll run this code:

Steve hit on one of the problems in trigger design, but there’s another problem that we often see. Steve’s code doesn’t have this problem, but quite often, people assume that rows update one at a time. But if you write a T-SQL UPDATE statement that affects five rows, the trigger fires once for all five. Steve correctly uses the inserted and deleted pseudo-tables to handle all changes, rather than just assuming a single row.

Comments closed

Capturing Database Object Changes in SQL Server

Published 2024-10-14 by Kevin Feasel

Sebastiao Pereira creates a trigger:

Data Definition Language (DDL) is a group of SQL statements that can be executed to manage database objects. The idea is to create a database trigger that tracks and logs all changes to database schemas, including modifications to stored procedures, tables, views, and other schema objects and storing any event in one table.

Read on for an example of a database-level trigger. These are much less likely to cause performance problems compared to table-level triggers, though you could certainly cause issues via poor trigger definition.

Comments closed

The Brokenness of TABLESAMPLE

Published 2024-09-10 by Kevin Feasel

Paul White walks us through some issues:

Initial testing went well, which was a pleasant surprise. Soon enough though, errors started to appear in the tool’s output. That’s not entirely unexpected since ensuring consistent results under high concurrency tends to expose all sorts of niggly edge cases. It’s still an annoyance because debugging edge cases in trigger code can be tricky and laborious.

What was a surprise though was the nature of the error messages.

Read on for the full story. Paul has also created a feedback issue covering a problem with the function.

Comments closed

Retaining SQL Agent Job History in a Managed Instance

Published 2024-06-19 by Kevin Feasel

Andy Brownsword gets around the limitations:

In a Managed Instance, the SQL Agent job history is fixed at 1000 records or 100 records per job. This isn’t configurable like a regular SQL Server install. So how can we maintain a history of these if we want to retain those records?

There are 3 approaches which could be worth considering. Two of these have been well covered by others and the final one I’ll demonstrate here:

Click through for those three techniques.

Comments closed

Trigger Order: Oracle vs Postgres

Published 2024-05-28 by Kevin Feasel

Deepak Mahto talks trigger ordering:

Database triggers allow encapsulation of multiple functionalities that are automatically invoked on specific events or DML operations like INSERT, UPDATE, or DELETE. The invocation of triggers can be controlled as BEFORE or AFTER the triggering events, either for each change or per statement. In migrating from Oracle to PostgreSQL, it is important to be aware of triggers conversion gotchas.

In this blog, we discuss the default order of processing when multiple triggers are defined on the same table for the same events with the same characteristics. We will explore how this works in Oracle, how to alter the order, and how it operates in PostgreSQL.

This is good to know, but I’d also consider it a major code smell to have multiple triggers on the same table.

Comments closed

Avoid ROLLBACK in Triggers

Published 2024-04-15 by Kevin Feasel

Thom Andrews shares a bit of advice:

A problem I’ve seen raised on numerous occasions is users that are getting a non-descript error when they are doing some kind of DML/DDL operation, and they don’t know why they are getting the error, and what is causing it. That error is:

Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Read on to learn more about good alternatives, remembering that you’re in the context of some other call when a trigger fires.

Comments closed