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.
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 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
DISABLE TRIGGER dbo.trgEmployeeUpdate ON dbo.Employee
Enable trigger reactivates the disabled trigger. Use the ENABLE TRIGGER statement to enable a trigger to fire when an event occurs.
ENABLE TRIGGER [schema_name.][trigger_name] ON [object_name | DATABASE | ALL SERVER]
The following enables the
ENABLE TRIGGER dbo.trgEmployeeUpdate ON dbo.Employee