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

SQL Server - How to identify the query that triggered a DML trigger on a table

Reading Time: 5 minutes

How to identify the query that triggered a DML trigger on a table? I read this question in a Telegram group and decided to write an article about it.

Introduction

As you know, there are several solutions to implement an audit log with data changes in a given table:

One of the most used solutions for this is the use of DML triggers (Learn how to implement this by clicking here)

In some scenarios, it is important to identify which query ended up triggering the trigger to perhaps identify the source of the data being inserted from SQL. And that's what we'll learn how to do in this post.

If you want more details on the origin of this command and identify the entire trigger execution chain, I suggest reading the article SQL Server - How to identify and monitor the execution of triggers.

Creating the test base

For the demonstration of the codes of this article, I will share with you the script I used to assemble this table and the trigger used to audit the changes. I will use the same example from the article SQL Server - How to Create a Data Change History for Your Tables (Audit Logs).

Creating the tables
Click here to view table creation code

Trigger creation:
Click here to view the trigger code

I make some changes to the original table:

And we can see that the audit is working as expected:

How to identify the query that triggered the trigger

Now we will find out how to identify the query that triggered the trigger.

For this, we will change the trigger and include the INPUTBUFFER command to capture the query and store this information in the log table.

The syntax for using the command is like this:

We will add the field that will store the query in the table:

And now we change the trigger:
Click here to view trigger code

And let's test it again by running the command below:

Result:

As the 2 SQL statements were executed together, in the same batch, the INPUTBUFFER command captured the 2 commands at once. If I try to execute it again, but using a GO to separate the blocks or even executing them separately, we can see that the trigger will now correctly identify the commands of each instruction:

Result:

And if you have another trigger, in another table, which is changing the Customer table and you are not able to identify where this command comes from?

First, we will create a table with employee data:

Now I will create a trigger on this new table that updates the Customer table if the customer and the employee have the same name:

When executing a simple UPDATE command, we will see that the trigger was fired on both tables

Result:

That Cool! Even the command coming from another trigger, it was possible to identify the origin of this change.

But what if you want more details of the origin of this command and identify the entire chain of trigger execution? Well, in this case, I suggest reading the article SQL Server - How to identify and monitor the execution of triggers.

Hope you enjoyed this article and until next time 🙂
Hugs!