SQL 2005+Disabling SQL Server Triggers
Triggers can be applied to the tables in a SQL Server database so that actions are taken automatically when data is inserted, updated or deleted. In some situations it is necessary to disable triggers to allow data to be modified without their effects.
Disabling Triggers
In a recent article I described how to disable constraints applied to tables. That article explained how to disable individual constraints or all of the constraints for a table. Disabling such constraints does not prevent triggers from executing when data is inserted, updated or deleted from the tables that they have been applied to. Several readers asked how triggers may be disabled in a similar manner.
NB: The commands described in this article should be used with care. Your triggers may be essential to maintaining the integrity of the database. With triggers disabled it may be possible to create invalid data. You should disable triggers for maintenance purposes only and not as part of standard processes. You should consider using single user mode to ensure other users cannot create data whilst triggers are inactive.
Disabling a Trigger
If you wish to disable an individual trigger, you can use the DISABLE TRIGGER command. This requires two pieces of information. The name of the trigger to be disabled is provided after the DISABLE TRIGGER clause and before the ON clause. The name of the table to which the trigger applies completes the command. For example, the statement below disables the DisallowBusinessNameChange trigger on the Customers table:
DISABLE TRIGGER DisallowBusinessNameChange ON Customers
To re-enable the trigger, use the ENABLE TRIGGER command. The syntax is similar to the above:
ENABLE TRIGGER DisallowBusinessNameChange ON Customers
Disabling All Triggers on a Table
If you are performing a bulk update on a table you may wish to disable all of its triggers for the duration of the process, applying further commands later to mimic the missed trigger actions. This can be more efficient than allowing the triggers to execute for every data modification. To disable all of the triggers for a table, replace the trigger name in the DISABLE TRIGGER command with ALL, as shown below:
DISABLE TRIGGER ALL ON Customers
A similar syntax is used to re-enable the triggers:
ENABLE TRIGGER ALL ON Customers
20 March 2011