SQL SERVER – Enabling or Disabling Triggers with the Correct Scope

To disable all triggers correctly, I first identify the required scope. My original customer case concerned expensive table-trigger work.

SELECT name,is_disabled,parent_class_desc FROM sys.triggers ORDER BY name;
SELECT name,is_disabled FROM sys.server_triggers ORDER BY name;
-- Targeted table trigger examples, after preserving original states:
-- DISABLE TRIGGER dbo.YourTrigger ON dbo.YourTable;
-- ENABLE TRIGGER dbo.YourTrigger ON dbo.YourTable;
-- Historical server-scope statements, not table-trigger controls:
-- DISABLE TRIGGER ALL ON ALL SERVER;
-- ENABLE TRIGGER ALL ON ALL SERVER;

Table or view DML triggers use ON schema.object. Database DDL triggers use ON DATABASE. Server DDL and logon triggers use ON ALL SERVER. The last form does not disable every table trigger.

The customer’s system improved when problematic trigger work was removed. That observation does not justify blanket disabling. Triggers can enforce auditing, business rules and replication behavior. Application replacements need every writer to follow the required workflow.

Inventory definitions and enabled states. Test a named change on a copy and preserve required behavior. Restore recorded states afterward. ENABLE ALL can unintentionally activate previously disabled triggers.

Reference: Trigger scope and permissions.

Related reading

• Comprehensive Database Performance Health Check
• SQL in Sixty Seconds video
• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

A server-scoped trigger command is not a database-wide table-trigger command, it is an operation in a different scope.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.