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