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