When is a trigger fired?

Question

My portal will be picking data every 2 hours from a file to a SQL Server table, lets say TableSource. As data is received in TableSource, it should also be copied into TableDestination, so we wrote a trigger for it.

My question is: what is the interval or rate of triggers getting fired in SQL Server? Will it be fired for each row (like MySQL), or after 500 inserts or after completion of all the inserts?

Data will be inserted in bulk, not manual entries.. maybe like 40k - 50k per hour. Can I run my trigger only once, after all my 40K are completely inserted into TableSource?

Answer 1

From Controlling Trigger Execution When Bulk Importing Data

A trigger is a special form of stored procedure that is executed automatically when a user modifies data in a table or view.

[...]

If triggers are enabled, they are executed once for each batch.

You can also track the behavior of the triggers. So you can make triggers to be executed only once per bulk import operation.

Another solution is to create a stored procedure, which will do exactly the same things that you are doing inside your triggers. You can call that SP after the bulk import operation completes in the same transaction.

---

BULK INSERT is a T-SQL command. Whenever you write a bulk insert you can specify the BATCHSIZE parameter:

BATCHSIZE = batch_size
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch.

Answer 2

On a FOR/AFTER type trigger, it gets fired immediately after each insert, update or delete statement, prior to commit.

An INSTEAD OF trigger works differently and actually provides alternate logic for your insert, update or delete. It fires in place of your insert, update or delete statement. In this case it doesn't sound like the right approach.

Use the inserted or deleted logical tables to capture the results of the insert or delete. Since you are doing bulk inserts, make sure to write the trigger to handle multiple rows at a time.

I'm not a big fan of the approach but I've seen a lot of developers using cursors in triggers. That tends to have negative performance impacts so I usually stick to normal insert statements.

One additional point, though, which I should clarify: if you are actually using the T-SQL BULK INSERT statement, it's possible that triggers will be skipped. There is an override, FIRE_TRIGGERS that you will want to make sure to use. From that documentation link:

FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

If FIRE_TRIGGERS is not specified, no insert triggers execute.