How to disable and enable a table trigger in SQL Server

Triggers are a useful feature of database management systems such as SQL Server and Oracle. They are not a required feature of a database table, they are optional so you can decide whether you want one or not.

A trigger is a stored procedure that is automatically execute when an operation such as an insert, update or delete occurs on a table row. They are executed per row not per operation.

There are times when you want to disable a trigger because you don't want the operation to happen and then to re-enable once you have completed the operation.

Disable Trigger on Table

To disable a trigger, you need to know the name of the trigger, you would do :-

Disable Trigger [schema].[TriggerName] on [TableName]

Alter table [TableName] Disable Trigger [schema].[TriggerName]

Disable All Triggers on the Table

There might be a case where you might have multiple triggers on the same table and you want them all disabled. You don't have to name all the triggers, you simply do :-

Disable Trigger All on [TableName]

Alter table [TableName] Disable Trigger All

Enable Trigger on Table

There's no point in disabling a trigger for ever, if you need to disable for ever, just remove it. However, once you have done what you've got to do with your trigger, you can re-enable it using the same formats as you would with the Disable but this time, change the disable command for enable. The below is an example for enabling trigger for a table.

Enable Trigger [schema].[TriggerName] on [TableName]

Alter table [TableName] Enable Trigger [schema].[TriggerName]

Likewise, replace the trigger name with ALL to re-enable all the triggers for a certain table.

Tags - SQL

Last Modified : June 2023