Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to identify and monitor the execution of triggers

Reading Time: 5 minutes

In an environment with many triggers being fired, it is often necessary to be able to identify and monitor the execution of triggers to analyze a certain behavior or understand how data is being changed.

This ends up getting even more complex when a trigger changes data from another table(s) and multiple triggers are fired in sequence, nested, from a single SQL command.

That's what you'll learn in this article.

One of the ways to be able to identify the DML command that triggered a trigger is to change the trigger itself to capture the SQL command.

See how to do this in the articles below:

Example:

Test base creation

For the demonstration of this post, I will share the scripts used so that you can test in your environment as well.

Creating the tables
Click here to view the table code

Creation of triggers
Click here to view the trigger code

How to identify and monitor the execution of triggers

Now that we have the tables and triggers created, let's start our monitoring, change some data and test that everything is working as expected.

Let's create an Extended Event (XE) to capture the execution of triggers:

Let's run some data changes to test our trigger monitor:

And now let's read the data collected from the Extended Event to view the triggers execution history

Result:

With this script, you can identify each command executed inside the trigger (statement column) or the command that triggered the trigger (sql_text column).

If you want something simpler and more concise, to know just when the triggers were fired, you can use this script here:

Result:

In this shorter version, it is very easy to identify that a single update activated 2 triggers by looking at the eventDate, nest_level and sql_text columns. After the UPDATE command, the trigger “trgFuncionario” was fired and as this trigger makes another UPDATE in the “Cliente” table, it activated the trigger “trgHistorico_Cliente”.

Now it's easy to identify triggers being fired in the environment, especially nested triggers like this example, where a single command can end up firing multiple triggers, making it difficult to track in a large environment.

Hope you enjoyed this tip and see you in the next article!
Hugs.