SQL Server Disable and Enable Triggers


By:
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.

Argument Description
Trigger_Name | ALL Trigger_Name: This is the name of the trigger you want to disable. ALL: When this keyword is used instead of a trigger name, then all of the triggers at the scope specified by the ON clause are disabled.
Object_Name | DATABASE | ALL SERVER Object_Name: Only valid for DML triggers. This is the name of the table or views were the trigger belongs. DATABASE: This indicates that the trigger(s) are database scoped. SERVER: This indicates that the trigger(s) are database scoped.

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.

Disabling triggers.

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.

Disabling a server scoped trigger.

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.

Argument Description
Trigger_Name | ALL Trigger_Name: This is the name of the trigger you want to enable. ALL: When this keyword is used instead of a trigger name, then all of the triggers at the scope specified by the ON clause are enabled.
Object_Name | DATABASE | ALL SERVER Object_Name: Only valid for DML triggers. This is the name of the table or views were the trigger belongs. DATABASE: This indicates that the trigger(s) are database scoped. SERVER: This indicates that the trigger(s) are database scoped.

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.

Enabling some triggers.

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.

Enabling a server scoped trigger.
Additional Information

Last Update: 8/2/2021




Comments For This Article





download














get free sql tips
agree to terms