asked
Trigger's invocations when mutiple rows are handled
I want to know how many times a trigger is invoked when multiple rows are handled? For, example if we have `INSTEAD OF INSERT` trigger and we are inserting 10 rows in the underlying table - is the trigger invoke for each row or for all of them? [Here ][1] is said that: > However, because an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, the insertion of many rows may cause a single trigger invocation. Is is always true, that when multiple rows are inserted, the trigger is invoked always only once? [1]:
https://msdn.microsoft.com/en-us/library/ms190752.aspx+invokatiossns+
If you are working inside a server instance, the trigger is fired once, and you will have zero or more rows in the INSERTED and DELETED virtual tables. It might behave a little different if you are working with linked servers. Let's say you have instance A and instance B. On instance A, you have a linked server definition to linked server B. If you then, on instance A do something like: INSERT INTO B.databasename.schemaname.tablename(column) SELECT column FROM databasename.schemaname.tablename you might end up with a row-by-row insert, and then I think the trigger will be fired once per row. If you instead make your insert query on instance B, and do: INSERT INTO databasename.schemaname.tablename(column) SELECT column FROM A.databasename.schemaname.tablename you will get the insert done in a single operation.
14 People are following this question.
