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:
create database local_db;
use local_db;
Next, define a table as shown:
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
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
END;
SQL Server Disable Trigger Query
Luckily, SQL Server provides us with a native method of disabling a trigger, as shown in the syntax below:
ON { object_name | DATABASE | ALL SERVER } [ ; ]
Query Arguments
The query accepts the following parameters:
- 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.
- trigger_name – the name of the trigger you wish to disable.
- ALL – this parameter allows all the triggers defined in the ON clause to be disabled at once.
- object_name – the name of the table or view on which the trigger resides.
- 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.
databases;
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:
local_db.databases;
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:
database;
Termination
In this post, we discussed how to use disable trigger commands in SQL Server to disable triggers at various object levels.
Thanks for reading!!
Source: linuxhint.com