bcp and insert fire any triggers they encounter while they copy data into a table, which increases the amount of time it takes to perform the copy (for example, bcp switches from fast mode to slow mode to fire the trigger). Use the disable trigger option of the alter table command to disable any triggers associated with a table before you copy in the data. You can use the disable trigger option to either disable all the triggers associated with the table, or you can specify a particular trigger to disable. However, any triggers you disable will not be fired after the copy is complete. If you require these triggers to insert, update, or delete any data, you will have to fire them manually.
alter table... disable trigger uses this syntax:
alter table [database_name.[owner_name].]table_name
{enable | disable } trigger [trigger_name]
Where table_name is the name of the table for which you are disabling triggers, and trigger_name is the name of the trigger you are disabling. For example, to disable the del_pub trigger in the pubs2 database:
alter table pubs2 disable del_pubs
If you do not specify a trigger, alter table disables all the triggers defined in the table.
Use alter table... enable trigger to reenable the triggers after the load database is complete. To reenable the del_pub trigger, issue:
alter table pubs2 enable del_pubs
You can use the disable trigger feature only if you
are the table owner or database administrator.
If a trigger includes any insert, update, or delete statements,
these statements will not run while the trigger is disabled, which
may affect the referential integrity of a table.
