Disable Trigger in SQL Server

In this SQL Server tutorial, we will understand how to disable a trigger in SQL Server either by using a T-SQL query or by using SQL Server Management Studio.

As Database Administrators, we might need to create multiple triggers on the same SQL Server Table. However, the role of these multiple triggers might be different based on different requirements.

But, if we create multiple triggers on the same table, all the triggers will be executed which could raise issues in our SQL Server Database. Now, to skip these issues, we can disable those triggers which are not necessary.

So, here we will cover the following set of topics

• How to disable trigger in SQL Server
• Disable Trigger in SQL Server Management Studio
• Disable Trigger in SQL Server using Transact SQL
• Enable Disable Trigger in SQL Server
• How to disable all trigger in SQL Server
• Disable Trigger in SQL Server Stored Procedure
• Find Disabled Trigger in SQL Server
• How to disable LOGON trigger in SQL Server
• Disable Trigger If Exists in SQL Server

Also, check the previous tutorial on SQL Server: Create Trigger in SQL Server for Insert and Update

Disable Trigger in SQL Server

Whenever we create a trigger in SQL Server, it will be enabled by default. And when we create multiple triggers on the same table, in that case, multiple triggers will be executed based on the specified same action.

Moreover, there could be multiple reasons where we might need to disable trigger in SQL Server.

Now, in SQL Server, we can either disable a trigger using SQL Server Management Studio or by using Transact-SQL query. But before we jump on to the steps to disable a trigger, we need a trigger in SQL Server.

So, here is the sample trigger that we are going to use for our examples in this tutorial.

USE [GeoNames]
GO

CREATE TRIGGER [dbo].[trgStatesInsert]
ON [dbo].[States]
AFTER INSERT	
AS
BEGIN
	DECLARE @state_name VARCHAR(20)

	SELECT @state_name = inserted.name FROM inserted
    PRINT 'New record with state name ' + @state_name + ' is inserted'
END
GO

The above trigger will return a success message when a new state record is inserted in the States table.

Once our trigger is created in SQL Server, it will be enabled by default. So, now, we can move to the next phase and understand different ways to disable triggers.

Disable Trigger in SQL Server Management Studio

Once we create a trigger in SQL Server, the trigger will be listed under the Triggers directory of that specific table in the SSMS.

For example, we have created the trgStatesInsert trigger on the States table which is located in the GeoNames database. We can find the trigger under the Triggers directory of the States table.

Now, to disable a trigger in SQL Server Management Studio, we need to right-click the required trigger and click on Disable.

Disable Trigger in SQL Server using Transact-SQL

Now, it is not always mandatory to use the SQL Server Management Studio. Instead, we can also use a Transact-SQL query to disable trigger in SQL Server.

For this task, we need to follow the following syntax in SQL Server.

DISABLE TRIGGER SCHEMA_NAME.YOUR_TRIGGER_NAME 
ON [YOUR_OBJECT_NAME | YOUR_DATABASE_NAME | ALL SERVER];

In this syntax explanation:

• First, we need to specify the name of the trigger that we want to disable with the DISABLE TRIGGER statement. However, please note that we can specify the trigger with or without its schema name.
• Second, specify the table name or view name to which the trigger is bound if the trigger was a DML trigger. We will use the DATABASE if the trigger is a DDL database-scoped trigger or use the SERVER option if the trigger is a DDL server-scoped trigger.

Now that we understand the syntax of how to disbale trigger in SQL Server using Trasact-SQL. Let us look at an example where we will disbale the same trigger using SQL query.

USE [GeoNames]
GO

DISABLE TRIGGER trgStatesInsert
ON dbo.States
GO

Once we execute the above example, we have successfully disabled the trgStatesInsert trigger which was created on the States table.

However, after disabling the trigger, if we try to perform the insert operation on the States table, the trigger will not be executed.

Also, check: SQL Server Trigger Update 

Enable Disable Trigger in SQL Server

In the previous section, we have seen how to disable trigger in SQL Server. Now, what if we want to enable that disabled trigger again? In that case, we need to understand how to enable the trigger in SQL Server.

So, just like disabling, we can enable the trigger in SQL Server either by using the SQL Server Management Studio or by using Transact-SQL query.

Let us discuss both methods in SQL Server.

Enable Disable Trigger in SQL Server Management Studio

To enable any disabled trigger in SQL Server Management Studio, we need to again right-click the required trigger and click on Enable option.

Enable Disable Trigger in SQL Server using Transact-SQL

Other than SQL Server Management Studio, we can also enable a disabled trigger in SQL Server using Transact-SQL query.

Here is the general syntax that we can follow to enable a disabled trigger in SQL Server.

ENABLE TRIGGER [your_trig_name] 
ON [your_obj_name | your_DATABASE | ALL SERVER];

The overall syntax to enable or disable a trigger in SQL Server is the same, we just need to replace ENABLE keyword with DISABLE.

Next, let us use the above syntax and enable the trgStatesInsert from the GeoNames database.

USE [GeoNames]
GO

ENABLE TRIGGER trgStatesInsert
ON dbo.States
GO

In the above SQL query, we have enabled the trgStatesInsert trigger which we created on the States table.

Read: SQL Server Trigger After Insert Update

Disable All Trigger in SQL Server

Till now, we have seen how to enable or disable a single trigger in SQL Server at a time. Now, in this section, we will take a step further and understand how to disable all triggers at once either from a table, database, or server in SQL Server.

Here is the syntax that we can follow to DISABLE ALL Triggers in SQL Server.

DISABLE TRIGGER ALL 
ON [YOUR_OBJ_NAME | YOUR_DATABASE_NAME | YOUR_SERVER_NAME];

In the above syntax, instead of specifying a trigger name, we use the ALL keyword to select all triggers at once. Moreover, we select all the riggers which are either created on a table, database, or the entire server.

In such cases, we need to mention the name of the table, database, or server.

Let us look at examples related to all these different cases in SQL Server.

Example-1: Disabling triggers on SQL Server Table

USE GeoNames
GO

DISABLE TRIGGER ALL 
ON dbo.States

By using the above query, we are disabling all triggers from the States table in SQL Server.

Example-2: Disabling triggers on Database

USE GeoNames
GO

DISABLE TRIGGER ALL 
ON DATABASE

In the above example, we are disabling all the triggers which are there in the current database.

Example-3: Disabling triggers on all servers

USE GeoNames
GO

DISABLE TRIGGER ALL 
ON ALL SERVER

In the above example, we have disabled all triggers which are active in all servers.

DISABLE TRIGGER statement.

So, in this section, we have understood how to disable all triggers in SQL Server either from a table, database, or all servers.

Read: Instead of Trigger In SQL Server

Disable Trigger in SQL Server Stored Procedure

In this SQL Server section, we will learn and understand how to disable trigger in SQL Server Stored Procedure.

Till now, we were executing the ENABLE / DISABLE Trigger statement directly in our query editor window. But, as we discussed there could be instances that there are multiple triggers on the same trigger. However, we cannot allow all the triggers to execute on the same table operation.

So, to overcome such situations in SQL Server, we can create a stored procedure in SQL Server which will enable and disable the specified triggers.

Next, let us consider an example where we have 2 AFTER INSERT Triggers for the States table. And we want to use only one trigger at a time. In such a case, we will create a stored procedure that will enable or disable the specified triggers from the States table.

Here is the T-SQL code for the stored procedure in SQL Server.

USE GeoNames
GO

CREATE OR ALTER PROCEDURE uspEnableDisableTrg
(
	@disable_trg VARCHAR(50), 
	@enable_trg VARCHAR(50) 
)
AS
BEGIN
	--Disabling Trigger
	EXEC('USE GeoNames; DISABLE TRIGGER ' + @disable_trg + ' ON dbo.States')
	PRINT @disable_trg+ ' is disabled successfully'

	--Enabling Trigger
	EXEC('USE GeoNames; ENABLE TRIGGER ' + @enable_trg + ' ON dbo.States')
	PRINT @enable_trg + ' is enabled successfully'
END

In the above Transact-SQL code, we have created a stored procedure named uspEnableDisableTrg. This stored procedure will take 2 input parameters and use these values as the names of the trigger that we want to enable or disable.

Within the stored procedure, we have used the EXEC() function to execute the ENABLE or DISABLE Trigger statement on the States table.

Now, we don’t need to execute the ENABLE and DISABLE Trigger statements separately. Instead, we can execute the above created stored procedure by specifying the trigger named that we want to enable or disable.

Here is how we can execute the uspEnableDisableTrg trigger in SQL Server.

USE GeoNames
GO

EXEC uspEnableDisableTrg @disable_trg = 'trgStatesInsert',
					      @enable_trg = 'trgStatesInsertNew'	

Once we execute the above stored procedure, it will disable the trgStatesInsert trigger and enable the trgStatesInsertNew. Moreover, we will get the following output as a result.

So, by the end of this section, we have understood how to disable trigger in SQL Server Stored Procedure.

Read: Trigger For Delete SQL Server

Find Disabled Trigger in SQL Server

In this SQL Server section, we will understand how to find the disabled trigger in SQL Server.

To find the disabled trigger in SQL Server, we can utilize the sys.triggers view. This is a system-based view in SQL Server that holds data related to trigger type objects.

Generally in SQL Server, DML trigger names are schema-scoped and consequently appear in the sys.objects view. However, the sys.triggers view can hold details of DDL trigger names since they are scoped by the parent entity.

Now, to find the disabled triggers from a database, we can easily query this view by selecting the required database. Here is the Transact-SQL statement that we can use to find disabled triggers from the GeoNames database.

USE GeoNames
GO

SELECT name, type_desc, is_disabled 
FROM sys.triggers
WHERE is_disabled = 1

In the above query, we are querying the sys.triggers view and using the WHERE clause to fetch only those triggers that are disabled.

Here is the result of the above T-SQL statement in SQL Server.

So, at the end of this SQL Server section, we get to know how to find disabled triggers in SQL Server.

Read: SQL Server User Permissions

Disable Logon Trigger in SQL Server

LOGON Triggers in SQL Server are different categories of triggers that are automatically executed based upon LOGON events.

The main role of using LOGON Triggers in SQL Server is to manage server sessions, keep track of login activities, and even limit login access.

Let us look at an example of a LOGON Trigger in SQL Server.

CREATE OR ALTER TRIGGER trgCheckLogin
ON ALL SERVER WITH EXECUTE AS N'test_user'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN() = N'test_user' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = N'test_user') > 3  
			ROLLBACK;  
END;

In the above example, we have created a LOGON Trigger in SQL Server which will block the login attempt made by the test_user. However, the trigger will block the attempt only when there are already 3 login sessions generated by that login.

Now, let us move to the next phase and understand how we can disable this LOGON Trigger in SQL Server.

To disable the LOGN Trigger, we can use the following Transact-SQL query in SQL Server.

DISABLE TRIGGER trgCheckLogin
ON ALL SERVER   
GO  

In the above Trsanct-SQL code, we have disabled the trgCheckLogin trigger. Now, as this trigger is a LOGON trigger, it comes under the Server Objects directory in SQL Server and SQL Server Management Studio.

Here is the result of the above execution in SQL Server.

So, in this section, we have understood how to disable the LOGON Trigger in SQL Server.

Read: Alter view in SQL Server

Disable Trigger If Exists in SQL Server

Now there will be many instances while working with triggers in SQL Server that we try to disable a trigger which does not in that particular database. And in such cases, we usually get the following error message from SQL Server.

Now, to handle these kind of errors in SQL Server, we can use the IF EXISTS statement to check the existence of a Trigger. And based upon the existence of the trigger, we will disable that trigger.

Let us look at an example of how to use IF EXISTS with DISABLE Trigger statement in SQL Server.

USE GeoNames
GO

IF EXISTS(SELECT name from sys.triggers
		WHERE type = 'TR' AND name = 'trgCountriesColumnTest')
DISABLE TRIGGER [trgCountriesColumnTest]
ON dbo.Countries
ELSE
BEGIN
	PRINT 'Trigger does not exit'
END

• In the above example, we have used the IF EXISTS statement to check the existence of a trigger using the sys.triggers view.
• And if the trigger exists, it will disable that particular trigger. However, if the trigger name does not exist in the sys.triggers view, it will print a statement stating “Trigger does not exist”.

So, with the end of this section, we have understood how to disable trigger in SQL Server only if it exist in the database.

You may also like to read the following SQL Server tutorials.

• How to call a view in SQL Server
• Indexed views in SQL Server
• View SQL Server Error Logs
• SQL Server logical operators

We now know how to Disable Trigger in SQL Server. Additionally, we talked about a few examples to help you better understand the idea. The full list of subjects we have addressed is shown below.

• How to disable trigger in SQL Server
• Disable Trigger in SQL Server Management Studio
• Disable Trigger in SQL Server using Transact SQL
• Enable Disable Trigger in SQL Server
• How to disable all trigger in SQL Server
• Disable Trigger in SQL Server Stored Procedure
• Find Disabled Trigger in SQL Server
• How to disable LOGON trigger in SQL Server
• Disable Trigger If Exists in SQL Server