SQL Server Disable and Enable Triggers

By: Daniel Farina

Overview

This chapter of the triggers tutorial will cover a side of triggers that is sometimes overlooked: disabling and re-enabling triggers.

Enabling and Disabling Triggers

Sometimes there are moments where you don't want a trigger to fire. For example, you may need to load data into an audit table and avoid triggers from firing. The first idea that could come to your mind is to save trigger's code into a script file and drop it, but there is a more practical solution which is to disable the trigger, do what you need to do and then enable the trigger again.

Disabling SQL Server Triggers

In order to disable any type of SQL Server triggers we use the DISABLE TRIGGER command.

DISABLE TRIGGER [Trigger_Name | ALL] ON [Object_Name | DATABASE | ALL SERVER]

Additionally the next table describes each of the arguments of the DISABLE TRIGGER statement.

Now let's disable some triggers in our sample database. In the next script I will disable all triggers in the Person view and the database scoped TR_Schema_Change trigger.

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.triggers;
 
DISABLE TRIGGER TR_Schema_Change ON DATABASE;
DISABLE TRIGGER ALL ON dbo.Person;
 
SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.triggers; 

As you may have already noticed, I also added two select statements to the system view sys.triggers so you can see the effect of the DISABLE TRIGGER command by looking at the is_disabled column. In the next image you can see the output of this script.

Additionally we can disable the server scoped trigger we created in the previous chapter. This time, instead of querying sys.triggers we have to query the sys.server_triggers system view.

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.server_triggers;
 
DISABLE TRIGGER TR_Login ON ALL SERVER;
 
SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.server_triggers;

In the next image you can see the output of the code.

Enabling SQL Server Triggers

The statement used to enable any type of SQL Server triggers is the ENABLE TRIGGER command.

ENABLE TRIGGER [Trigger_Name | ALL] ON [Object_Name | DATABASE | ALL SERVER]

Additionally the next table describes each of the arguments of the ENABLE TRIGGER statement.

In order to show you how to use this command let's enable the triggers we disabled.

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.triggers;
 
ENABLE TRIGGER TR_Schema_Change ON DATABASE;
ENABLE TRIGGER ALL ON dbo.Person;
 
SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.triggers;

Take a look at the following screen capture to see the output of the code section above.

Also, let's enable the server scoped trigger.

SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.server_triggers;
 
ENABLE TRIGGER TR_Login ON ALL SERVER;
 
SELECT name,
       parent_class_desc,
       type_desc,
       is_disabled
FROM sys.server_triggers;

In the next image you will see the results of executing the code above.

Additional Information

• Auditing when Triggers are Disabled or Enabled for SQL Server
• Disabling a Trigger for a Specific SQL Statement or Session