SQL Server DISABLE TRIGGER

Summary: In this tutorial, you will learn how to use the SQL Server DISABLE TRIGGER statement to disable a trigger.

Introduction SQL Server DISABLE TRIGGER #

Sometimes, for the troubleshooting or data recovering purpose, you may want to disable a trigger temporarily. To do this, you use the DISABLE TRIGGER statement:

DISABLE TRIGGER [schema_name.][trigger_name] 
ON [object_name | DATABASE | ALL SERVER]
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, 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 keywords.
• Second, specify the table name or view that the trigger was bound to if the trigger is a DML trigger. Use DATABASE if the trigger is DDL database-scoped trigger, or SERVER if the trigger is DDL server-scoped trigger.

SQL Server DISABLE TRIGGER example #

The following statement creates a new table named sales.members for the demonstration:

CREATE TABLE sales.members (
    member_id INT IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    member_level CHAR(10) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

The following statement creates a trigger that is fired whenever a new row is inserted into the sales.members table. For the demonstration purpose, the trigger just returns a simple message.

CREATE TRIGGER sales.trg_members_insert
ON sales.members
AFTER INSERT
AS
BEGIN
    PRINT 'A new member has been inserted';
END;
Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new row into the sales.members table:

INSERT INTO sales.members(customer_id, member_level)
VALUES(1,'Silver');
Code language: SQL (Structured Query Language) (sql)

Because of the INSERT event, the triggered was fired and printed out the following message:

A new member has been inserted
Code language: SQL (Structured Query Language) (sql)

To disable the sales.trg_members_insert trigger, you use the following DISABLE TRIGGER statement:

DISABLE TRIGGER sales.trg_members_insert 
ON sales.members;
Code language: SQL (Structured Query Language) (sql)

Now if you insert a new row into the sales.members table, the trigger will not be fired.

INSERT INTO sales.members(customer_id, member_level)
VALUES(2,'Gold');
Code language: SQL (Structured Query Language) (sql)

It means that the trigger has been disabled.

Note that the trigger definition is still there on the table. If you view the trigger in the SQL Server Management Studio (SSMS), you will notice a red cross icon on the disabled trigger name: