Disable Trigger in SQL Server

In this  SQL Server tutorial, we will learn and comprehend how to use the Disable Trigger in the SQL Server statement. We will discuss and learn several instances to assist you in better understanding the concept. The full list of topics we will cover is given below.

  • Disable Trigger in SQL Server
  • Enable Disable Trigger in SQL Server
  • Disable All Trigger in SQL Server
  • Disable Trigger in SQL Server Stored Procedure
  • Find Disabled Trigger in SQL Server
  • 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

Sometimes for troubleshooting or data recovering purposes, we want to disable a trigger temporarily. Here is the syntax of the SQL Server DISABLE TRIGGER statement by the following query:

SYNTAX:

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 schema to which the trigger belongs and the name of the trigger that you want to disable after the DISABLE TRIGGER keyword.
  • Second, specify the table name or view to which the trigger bounds to 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 if the trigger is a DDL server scoped trigger.

Let’s see a demonstrated example of the SQL Server DISABLE TRIGGER statement on the USA_STATES table by the following query:

EXAMPLE:

DISABLE TRIGGER INSTEADOF_INSERTUPDATE 
ON USA_STATES;

INSERT INTO USA_STATES VALUES (29,'Wisconsin',45231,'Martin Luther','Male');

In this preceding query, we have disabled a trigger called INSTEADOF_INSERTUPDATE on the USA_STATES table by using the DISABLE TRIGGER statement. If we try to insert a new record into the USA_STATES table by using the INSERT INTO statement then it will not be fired as the trigger has been disabled.

We hope that you have understood how to use the SQL Server DISABLE TRIGGER statement on the table by the query. For a better reason, we have used an illustration and demonstrated it in depth.

Also, check: SQL Server Trigger Update 

Enable Disable Trigger in SQL Server

We will learn and understand how to use the SQL Server ENABLE TRIGGER and DISABLE TRIGGER statements on the table by the query. And which will be explained with the help of syntax and an illustrated example.

The SQL Server ENABLE TRIGGER is used to reactivate the disabled trigger In the ENABLE TRIGGER statement to enable a trigger to fire when an event occurs. Here is the syntax of the SQL Server ENABLE TRIGGER statement on the table by the following query:

SYNTAX:

ENABLE TRIGGER [your_trigger_name] 
ON [your_object_name | your_DATABASE | ALL SERVER];

Let’s see an illustrated example of the SQL Server ENABLE TRIGGER statement on the table by the following query:

EXAMPLE:

ENABLE TRIGGER INSTEDOF_INSERTUPDATE 
ON USA_STATES;

The above query means that it will enable a trigger called INSTEADOF_INSERTUPDATE on the USA_STATES table by using the ENABLE TRIGGER statement. This means that once a trigger is fired on the table then it will generate or update or delete the record from the USA_STATES table. When the trigger is created in the SQL Server, it is automatically enabled by default.

The SQL Server DISABLE TRIGGER statement is used to disable a trigger temporarily from the table. Disable trigger doesn’t delete trigger from the database. The trigger exists in the current database but it doesn’t fire. Let’s see the syntax of the SQL Server DISABLE TRIGGER statement by the following query:

SYNTAX:

DISABLE TRIGGER [SCHEMA_NAME].[YOUR_TRIGGER_NAME]
ON [ OBJECT_NAME | DATABASE | ALL SERVER];

Here is a sample example of the SQL Server DISABLE TRIGGER statement on the table by the following query:

EXAMPLE:

DISABLE TRIGGER INSTEADOF_INSERTUPDATE
ON CANADA_STATES;

As we see in the above query, we have disabled a trigger called INSTEADOF_INSERTUPDATE on the CANADA_STATES table by using the DISABLE TRIGGER statement. This means that it will disable the trigger from the table but it will not delete it from the database. So, it will not fire as the trigger has been disabled.

We hope that you have understood the concept of the SQL Server ENABLE TRIGGER and DISABLE TRIGGER statement on the table by the query. For a better experience, we have used an illustration and described it in depth.

Read: SQL Server Trigger After Insert Update

Disable All Trigger in SQL Server

Here we will learn and understand how to disable all triggers on tables by the query. And which will be explained with the help of syntax and an illustrated example.

SYNTAX:

DISABLE TRIGGER ALL ON YOUR_TABLE_NAME;

In this statement, we need to specify the name of the table to disable all triggers which belong to that table.

Here is an illustrated example of the SQL Server DISABLE TRIGGER statement to remove all triggers on the table by the following query:

EXAMPLE:

DISABLE TRIGGER ALL ON USA_STATES;

In this above query, we are disabling all triggers from the USA_STATES table by using the DISABLE TRIGGER statement.

We hope that you have understood the subtopic “Disable All Trigger in SQL Server” by using the DISABLE TRIGGER statement on the table by the query. For a better explanation, we have used an example and explained it in depth.

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 use the DISABLE TRIGGER statement on the STORE PROCEDURE by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

ALTER PROCEDURE SAFETY_ONDATABASE
AS
BEGIN
   EXEC('DISABLE TRIGGER AFTER_TIMESTAMPUSA ON sqlserverguides'); 
   EXEC('ENABLE TRIGGER TR_UPDATEMIT ON sqlserverguides');
END;

In the preceding query, we have used the ALTER PROCEDURE statement to stop a procedure name as SAFETY_ONDATABASE. In the BEGIN statement, we have used the EXEC statement to disable trigger names as AFTER_TIMESTAMPUSA and TR_UPDATEMIT from the SQLSERVERGUIDES database. To close it, we have used the END statement.

We hope that you have understood the subtopic “Disable Trigger SQL Server Stored Procedure” by using the ALTER PROCEDURE statement.

Read: Trigger For Delete SQL Server

Find Disabled Trigger in SQL Server

Here we will learn and understand how to use the SQL Server SELECT statement to find all disabled triggers from the database by the query. And which will be explained with the help of an illustrated example.

Each trigger object is represented by a row with a type of TR or TA. DML trigger names are visible in sys. objects since they are schema-scoped. DDL trigger names are only available in this view and are scoped by the parent entity. The IS_DISABLED column is used to disable a trigger from the system and returns the disable trigger as 1 and enable trigger as 0 from the sys. triggers.

Here is an illustrated example of the SQL Server SELECT statement to retrieve the disable all trigger from the system by the following query:

EXAMPLE:

SELECT NAME, IS_DISABLED FROM SYS.TRIGGERS;

In this preceding query, the SELECT statement is used to retrieve all records of the NAME and IS_DISABLED columns from the SYS.TRIGGERS table. The NAME column will tell all the trigger names created in the SYS.TRIGGERS table and the IS_DISABLED column will tell whether that trigger is disabled temporarily from the database.

If the trigger is temporarily disabled then it will refer to as 1 in the output column result set. And if the trigger is enabled then it will refer to as 0 in the output column.

Find Disabled Trigger in SQL Server example
SQL Server SELECT statement to find the disabled triggers in sys. triggers.

We hope that you have understood how to use the SQL Server ENABLE TRIGGER and SQL Server DISABLE TRIGGER statements on the table by the query. For a more reasonable acquaintance, we have used an example and clarified it in depth.

Read: SQL Server User Permissions

Disable Logon Trigger in SQL Server

Here we will learn and understand how to disable the LOGON trigger ON DATABASE and ALL SERVER by the query. And which will be explained with the help of an illustrated example.

A user must have CONTROL SERVER permission on the server to stop a login trigger or a DDL trigger with server scope (ON ALL SERVER). A user must, at the very least, have the ALTER ANY DATABASE DDL TRIGGER permission in the current database to disable a DDL trigger with database scope (ON DATABASE).

EXAMPLE:

CREATE TRIGGER SAFETY_ONDATABASE   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;  

DISABLE TRIGGER SAFETY_ONDATABASE ON DATABASE;

DISABLE TRIGGER ALL ON ALL SERVER;

In the first query, we have created a trigger called SAFETY_ONDATABASE on the current database. Then we have used the FOR trigger on the DROP_TABLE and ALTER_TABLE statements which means that once the trigger is triggered then the DDL scoped trigger is fired. In the AS clause, we have used the PRINT function to print for the output as ‘You must disable Trigger “safety” to drop or alter tables!’.

In the second query, we have disabled a trigger called SAFETY_ONDATABASE from the database by using the DISABLE TRIGGER statement. If we want to disable all trigger names from the ALL SERVER then we will use the DISABLE TRIGGER statement.

We hope that you have understood the subtopic “Disable Logon Trigger SQL Server” by using the DISABLE TRIGGER statement by the query. For a better explanation, we have used an example and explained it in depth.

Read: Alter view in SQL Server

Disable Trigger If Exists in SQL Server

In this section we will learn and understand how to disable trigger if exists in SQL Server by using the IF EXISTS clause on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

IF EXISTS (SELECT name FROM sys.triggers where type= 'TR' and name like '%e')
DISABLE TRIGGER INSTEADOF_INSERTUPDATE 
ON CANADA_STATES
GO
EXEC('ALTER TRIGGER INSTEADOF_CANADA
ON CANADA_STATES
INSTEAD OF INSERT
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=10');

INSERT INTO CANADA_STATES
VALUES(25,'Henry Cavil','Newfoundland and Labrador',5421);

In this aforementioned query, we have used the IF EXISTS clause to check whether the old trigger exists in the database or not in the table. In the SELECT statement, it will retrieve all records from the sys. triggers table which is based on the WHERE condition.

In the WHERE condition, the TYPE column is used with the EQUAL TO operator to find a string value as TR. And in the NAME column, the LIKE clause is used to find a trigger name whose name ends with the ‘%e’ from the sys. triggers table.

After that, we disabled a trigger called INSTEADOF_INSERTUPDATE from the CANADA_STATES table by using the DISABLE TRIGGER statement.

Using the ALTER TRIGGER statement, we have modified the INSTEADOF_CANADA trigger on the CANADA_STATES database in the EXEC statement. The INSERT statement will therefore skip the execution of the DML STATEMENTS and make any changes to the CANADA_STATES table because the INSTEAD OF trigger was used on the INSERT statement.

Instead, the SQL STATEMENTS used in the ALTER TRIGGER statement will be executed and run. In the AS clause, the SELECT statement is used to retrieve all records from the CANADA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 10 from the CANADA_STATES table.

As the INSTEAD OF trigger is fired then the DML STATEMENTS execution will be executed but it will make any effect on the CANADA_STATES table. Here is the execution of the INSTEAD OF trigger says:

  • In the INSERT INTO statement, it will insert a new record into the CANADA_STATES table.
disable trigger if exists sql server
Example of SQL Server IF EXISTS statement used with the DISABLE TRIGGER statement on the table.

We hope that you have understood the subtopic “Disable Trigger if Exists SQL Server” by using the SQL Server IF EXISTS used with the DISABLE TRIGGER statement on the table by the query. For a better explanation, we have used an example and explained it in depth.

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

We now know how Disable Trigger in SQL Server statement after reading this lesson. 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.

  • Disable Trigger in SQL Server
  • Enable Disable Trigger in SQL Server
  • Disable All Trigger in SQL Server
  • Disable Trigger in SQL Server Stored Procedure
  • Find Disabled Trigger in SQL Server
  • Disable Logon Trigger in SQL Server
  • Disable Trigger If Exists in SQL Server