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"