How to disable all the triggers in a SQL Server database

Introduction

This script is to disable all the triggers in a SQL Server database.

Scenarios

Sometimes, people want to disable all the triggers in a database for some reason. It will be a heavy task to check if the triggers in tables are disabled or not one by one. This script will help find all the triggers in all the tables in a database, and it will generate the following items:

• a table to show the status of all the triggers
• code in Messages panel to disable all the triggers in Enable status

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a “New Query”, copy the code from DisableAllTriggers.sql, paste it and run the script.

After the script finishes running, we’ll get the following figure:

 

Code in the Messages panel:

 

3. Copy the code in the Messages panel and execute it. You will see the following result:

 

4. Check the status of triggers with the following code:

SQL
Edit|Remove
mysql
SELECT  SCHEMA_NAME(schema_id) AS SchemaName,
        OBJECT_NAME(parent_object_id) AS TableName ,
        name AS TriggerName ,
        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
          WHEN 1 THEN 'Disabled'
          ELSE 'Enabled'
        END AS status
FROM    sys.objects
WHERE   type = 'TR'
ORDER BY OBJECT_NAME(parent_object_id)
 

SELECT  SCHEMA_NAME(schema_id) AS SchemaName, 
        OBJECT_NAME(parent_object_id) AS TableName , 
        name AS TriggerName , 
        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate , 
        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete , 
        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert , 
        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter , 
        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof , 
        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled') 
          WHEN 1 THEN 'Disabled' 
          ELSE 'Enabled' 
        END AS status 
FROM    sys.objects 
WHERE   type = 'TR' 
ORDER BY OBJECT_NAME(parent_object_id) 
 



 

 

You will see the following result:

 

Prerequisites

SQL Server 2005 or higher version