Enable or Disable Triggers in SQL Server
Triggers when created in SQL Server are enabled by default. You can disable a trigger temporarily using the DISABLE TRIGGER statement.
Disable trigger does not delete the trigger. The trigger exists in the current database but it doesn't fire.
Syntax:
DISABLE TRIGGER [schema_name.][trigger_name]
ON [object_name | DATABASE | ALL SERVER]
In the above syntax, trigger_name is the name of the trigger to be disabled under the schema_name schema.
Schema name cannot be specified for DDL triggers. Specify DATABASE for a database scoped DDL trigger and ALL SERVER for a server scoped DDL trigger.
The following disables the trgEmployeeUpdate trigger.
Example: Disable a Trigger
DISABLE TRIGGER dbo.trgEmployeeUpdate
ON dbo.Employee
Enable Triggers
Enable trigger reactivates the disabled trigger. Use the ENABLE TRIGGER statement to enable a trigger to fire when an event occurs.
Example:
ENABLE TRIGGER [schema_name.][trigger_name]
ON [object_name | DATABASE | ALL SERVER]
The following enables the trgEmployeeUpdate trigger.
Example:
ENABLE TRIGGER dbo.trgEmployeeUpdate
ON dbo.Employee