How often do Update triggers fire on a multi-record update?

Question

I have created an on update trigger.

If I am updating 5 records in the table in a single statement, how many times will the trigger execute? Does it change if I have multiple statements updating the records in a single transaction?

Does it execute 5 times, or only once after all the transactions are complete?

Solution 1

It all depends on the type of trigger you are using.

• a row level trigger will fire for each and every row that is affected by the DML statement (note this is also true for INSERT statements that are based on a SELECT or are using a multi-row syntax to insert more than one row at a time)
• a statement level trigger will fire once for the whole statement.

Oracle, PostgreSQL and DB2 support both, row level and statement level triggers. Microsoft SQL Server only supports statement level triggers and MySQL only supports row level triggers.

Solution 2

With SQL 2008: If you are doing 1 update that updates 5 rows, the trigger should be executed only once.

That's why you have to use the tables "INSERTED" and "DELETED" to be able to detect all the modified rows.

If you are doing 5 updates that update 1 row, the trigger will be executed 5 times.