| by Arround The Web | No comments

SQL Server Disable Trigger

“A trigger is a stored procedure executed when a specific action/event occurs in the server. Various types of triggers can be associated with particular actions.

This post aims to show you how you can disable an existing trigger in SQL Server. Disabling a trigger can temporarily allow you to perform an action without a subsequent event. This is very useful when troubleshooting or performing maintenance operations on the server.”

Creating a Test Trigger

Before discussing how to disable an existing trigger in the SQL server, let us start by defining a simple trigger for illustration purposes.

Start by creating a database:

drop database if exists local_db;
create database local_db;
use local_db;

Next, define a table as shown:

create table databases(
    id int not null identity(1,1) primary key,
    server_name varchar(50),
    server_address varchar(255) not null,
    compression_method varchar(100) default 'none',
    size_on_disk float not null,
    size_compressed float,
    total_records int not null,
    init_date date

Next, create a trigger to disable the number of affected rows when an insert or delete action occurs.

ON databases


SQL Server Disable Trigger Query

Luckily, SQL Server provides us with a native method of disabling a trigger, as shown in the syntax below:

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }  
ON { object_name | DATABASE | ALL SERVER } [ ; ]


Query Arguments

The query accepts the following parameters:

    1. schema_name – this defines the name of the schema on which the trigger resides. The shcema_name parameter is not supported for Data Definition Language Triggers or logon triggers.
    2. trigger_name – the name of the trigger you wish to disable.
    3. ALL – this parameter allows all the triggers defined in the ON clause to be disabled at once.
    4. object_name – the name of the table or view on which the trigger resides.
    5. DATABASE – specifies the scope of the DDL trigger.

Depending on the target user and server configuration, the disable trigger query requires ALTER permission on the table or view.

Example – Disabling a DML Trigger on a Table

The following example demonstrates how to disable the trigger nostatus on the databases table.

disable trigger databases.nostatus on

Running the statement above should disable the trigger with the specified name. This ensures that the trigger is not fired on insert or delete actions.

Example 2 – Disable Trigger Using SSMS

You can also disable a trigger using the SQL Server Management Studio. Open the Object Explorer. Locate the target database -> Target Table – Triggers.

Right-click and select disable.

Once successful, you should see a success dialog box.

Example 3 – SQL Server Disable All Triggers on a Table/View

You can also disable all triggers in a given table or view using the command provided in the snippet below:

disable trigger all on

The above query will disable all the triggers in the databases table.

Example 4 – SQL Server Disable All Triggers in a Database

Suppose you wish to perform a database-wide trigger disable. You can execute a query as provided below:

disable trigger all on



In this post, we discussed how to use disable trigger commands in SQL Server to disable triggers at various object levels.

Thanks for reading!!

Share Button

Source: linuxhint.com

Leave a Reply