Disable Triggers

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. The insert, update, and delete commands normally fire any trigger they encounter, which increases the time needed to perform the operation.

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
Note: 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.