Understanding Trigger in Azure SQL Database

Question

I have set up a trigger on a table in an Azure SQL Database. This trigger is configured to fire on INSERT operations, and it triggers a stored procedure (SP) whenever an insert occurs.

I would like to understand the behavior when inserting multiple rows into the table using a single INSERT statement, like in the example below:

INSERT INTO table

SELECT 1, 2

UNION

SELECT 3, 4

UNION

SELECT 4, 5;

In this case, will the trigger (and subsequently the stored procedure) execute only once for the entire INSERT statement, or will it execute three times, once for each row inserted?

Answer 1

Hi @ Ahwan Mishra,

Thanks for the question and using MS Q&A platform.

It's important to note that the exact behavior of triggers can depend on various factors, such as the transaction isolation level and the presence of other triggers on the same table. Therefore, it's always a good idea to test your triggers thoroughly to ensure that they behave as expected.

when using a basic INSERT statement to insert a single row, the trigger will fire once for that single row. However, when using an INSERT INTO (table_name) SELECT statement to insert many rows, the trigger may fire once for the entire batch of rows inserted.

For additional Information, please refer to this Document: Create DML Triggers to Handle Multiple Rows of Data.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful.

Answer 2

execute only once for the entire

The behavior of Azure SQL is the same as SQL Server on-premise.

A trigger fires once per transaction, not per record.

So the virtuall table "inserted" can contain 0-n records.