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.