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. Executing triggers can affect the performance of a bulk import operation. For example, a trigger that sends an e-mail message each time a record is imported reduces the speed of a bulk import operation, and creates a flurry of e-mail.
When bulk importing data, you can control whether triggers are executed (fired) by the bulk-import operation. A bulk-import operation should execute triggers only for a table with INSERT and INSTEAD OF triggers that support multiple row inserts. For more information about these triggers, see DML Triggers.
Important
When triggers are disabled, a schema modify lock might be taken to update the metadata. This can interfere with other commands (such as an online index build) or transactions. For example, a snapshot isolation transaction accessing the target table might fail due to concurrent DDL changes.
If triggers are enabled, they are executed once for each batch.
During a bulk-import operation, behavior depends on the command used for the operation. By default, the bcp command and BULK INSERT (Transact-SQL) statement disable triggers. In contrast, for the INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, the default is to execute triggers.
The following table summarizes the default behavior.
Bulk import command |
Default behavior |
|---|---|
bcp |
Disable triggers |
BULK INSERT |
Disable triggers |
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
Execute triggers |
Each of the bulk-import commands provides a qualifier that allows you to change how triggers are handled, as described in the following sections.
Executing Triggers with bcp or BULK INSERT
By default, the bcp command and the BULK INSERT statement do not execute triggers. However, you can enable triggers by using the following qualifiers:
Command |
Qualifier |
Qualifier type |
|---|---|---|
bcp |
-h"FIRE_TRIGGERS" |
Hint |
BULK INSERT |
FIRE_TRIGGERS |
Argument |
If FIRE_TRIGGERS is specified for a bulk-import operation, it executes any INSERT and INSTEAD OF triggers that are defined on the table for all rows inserted into the table.
For more information, see bcp Utility and BULK INSERT (Transact-SQL).
Disabling Triggers in INSERT ... SELECT * FROM OPENROWSET(BULK...)
By default, the OPENROWSET bulk rowset provider executes triggers. You can disable triggers by using the following qualifier:
Command |
Qualifier |
Qualifier type |
|---|---|---|
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
WITH(IGNORE_TRIGGERS) |
Table hint |
If this hint is specified, triggers are not executed by the OPENROWSET bulk rowset provider. For more information about the IGNORE_TRIGGERS hint, see Table Hints (Transact-SQL).
Importing Large Batches with Triggers Enabled
SQL Server 2005 and later versions use row versioning for triggers and stores the row versions in the version store in tempdb. Before you can bulk import a large batch of data records using triggers, you may need to expand the size of tempdb to accommodate the impact of the triggers on the version store. For more information, see Row Versioning Resource Usage.