When is a trigger fired? 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?
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.