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]

or

    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]

Or

    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]

or

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

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

Other Articles of Interest

Next Article : How to get the Month, Year or Day from a DateTime field in SQL
Previous Article : How to use Database Transactions with .NET CodeFirst Entity Framework

Tags - SQL

Comments and Questions

There's no register feature and no need to give an email address if you don't want to. All messages will be reviewed before being displayed. Comments may be merged or altered slightly, such as if it contains an email or website address.

You can decline to give a name; if that is the case, the comment will be attributed to a random star. A name is preferred, even if it's a random made-up one by yourself.

If you give an email address, you may receive an email notifying you when someone else has added a comment to the same page. In the email will be a link to unsubscribe to further notifications.