SYSK 220: Triggers and Bulk Insert of Data

Fact: triggers execute automatically whenever user modifies data in the underlying table or view. In my projects, I frequently use triggers when comprehensive auditing is a requirement. However, I’ve seen some developers use triggers as a mechanism to kick off a workflow of sort, e.g. send an e-mail or start some process. Having such a trigger fire during a bulk load operation would have undesired consequences on both – the performance and the number of workflow instances started.

For this reason, if data is inserted via BULK INSERT command or using bcp.exe utility, by default, triggers are disabled. This is not the case when using INSERT… SELECT * FROM OPENROWSET (BULK...) command – in this case triggers are enabled by default and are executed once for each batch.

However, you can change this default behavior. If you would like to enable triggers during bulk insert (BCP or BULK INSERT command), use FIRE_TRIGGERS argument. To prevent triggers from firing during INSERT… FROM OPENROWSET(BULK…) command, use the new WITH (IGNORE_TRIGGERS) hint.