The insert, update, and delete commands normally fire any trigger they encounter, which increases the time needed to perform the operation.To disable triggers during bulk insert, update, or delete operations, use the disable trigger option of the alter table command. You can use this option either to disable all triggers associated with the table, or to specify a particular trigger to disable. However, any triggers you disable are fired after the copy is complete.
bcp, to maintain maximum speed for loading data, does not fire rules and triggers.To find any rows that violate rules and triggers, copy the data into the table and run queries or stored procedures that test the rule or trigger conditions.
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.
