This blog explains how to disable and enable a particular trigger, all triggers of a table and all triggers for a database.

Disable a Particular Trigger:

Syntax:

ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name
Example:

ALTER TABLE Employee DISABLE TRIGGER TR_Insert_Salary
Enable a Particular Trigger:

Syntax:

ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name
Example:

ALTER TABLE Employee ENABLE TRIGGER TR_Insert_Salary
Disable All Trigger of a table:

We can disable and enable all triggers of a table using previous query, but replacing the "ALL" instead of trigger name.

Syntax:

ALTER TABLE Table_Name DISABLE TRIGGER ALL
Example:

ALTER TABLE Demo DISABLE TRIGGER ALL
Enable All Trigger of a table:

Syntax:

ALTER TABLE Table_Name ENABLE TRIGGER ALL
Example:

ALTER TABLE Demo ENABLE TRIGGER ALL
Disable All Trigger for database:

Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database.
Syntax:

Use Database_Name
Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
Example:

Use Demo
Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
Enable All Trigger for database:

Syntax:

Use Database_Name
Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
Example:

Use Demo
Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"