How often do Update triggers fire on a multi-recor

2020-04-05 06:56发布

问题:

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?

回答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.



回答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.



回答3:

Considering you are using SQL Server, the trigger will only fire once every Update.

If this is not what you want, you could consider using different update statements to make sure the trigger fires everytime.

You can look at this turorial on SQL triggers. It covers everything.

Note that if you are using Oracle the trigger can be based on rows. Not in SQL Server.