TSQL Tuesday: “Disabling” Triggers Without ALTERs
I’ve worked with my share of Triggers over the years. Most of the time, they were relatively simple affairs – do a simple update to a date if a row changed or track some basic audits somewhere. Other times they performed some necessary, but complex, function such as updating family names or calculating some grand total. Regardless, there are times you don’t want the complex portions to run. That normally requires ALTER TABLE right to disable the trigger, but with a little clever coding there are some other tricks to use.
The most interesting and easiest method is to add a check against some Trigger Status type table. This looks for the name of the trigger and whether it’s enabled or not. The majority of the trigger code is wrapped in the IF EXISTS block, with the parts you want to run regardless outside of that. While this isn’t truly “disabling” the trigger, it does allow for a way to not run more complex code when necessary, for example mass imports or mass updates.
The example code below will demonstrate a very simplified example of some tables and a trigger that will write the old values to an Audit type table, but only if the trigger is marked as Enabled in the Trigger Status table. Note that one of the dangers of triggers is that if one is created following this pattern and the matching row is not inserted in the TriggerStatus table, that section of code will never run and it won’t be visible.
I’ve found this trick useful to provide a way to “disable” major blocks in a trigger without needing to grant someone access to ALTER the schema. Combine this with a job to re-enable the trigger periodically to make sure that nothing important is missed because someone forgot to re-enable the trigger in the TriggerStatus table.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE TriggerStatus ( | |
| TriggerStatusID INT IDENTITY(1,1) NOT NULL, | |
| TriggerName varchar(100) NOT NULL, | |
| IsEnabled BIT NOT NULL CONSTRAINT DF_TriggerStatus_IsEnabled DEFAULT 1, | |
| CONSTRAINT PK_TriggerStatus PRIMARY KEY CLUSTERED (TriggerStatusID) | |
| ) | |
| GO | |
| CREATE TABLE MyAuditTable ( | |
| MyAuditTableID INT NOT NULL IDENTITY(1,1), | |
| TableName varchar(100) NOT NULL, | |
| TableID INT NOT NULL, | |
| OldValue VARCHAR(100) NOT NULL, | |
| CONSTRAINT PK_MyAuditTable PRIMARY KEY CLUSTERED (MyAuditTableID) | |
| ) | |
| GO | |
| CREATE TABLE dbo.MyExampleTable ( | |
| MyExampleTableID INT IDENTITY(1,1) NOT NULL, | |
| MyExampleTableName VARCHAR(100) NOT NULL, | |
| CreatedDate DATETIME2 NOT NULL CONSTRAINT DF_MyExampleTable_CreatedDate DEFAULT GETUTCDATE(), | |
| UpdatedDate DATETIME2 NULL, | |
| CONSTRAINT PK_MyExampleTable PRIMARY KEY CLUSTERED (MyExampleTableID) | |
| ) | |
| GO | |
| CREATE TRIGGER UpdMyExampleTable ON dbo.MyExampleTable FOR UPDATE | |
| AS | |
| BEGIN –Trigger | |
| UPDATE met SET met.UpdatedDate = GETUTCDATE() | |
| FROM dbo.MyExampleTable AS met | |
| JOIN Inserted AS i | |
| ON i.MyExampleTableID = met.MyExampleTableID | |
| –Check for both the row existing in the TriggerStatus table AND that the trigger is marked as enabled. | |
| –Lack of a row will cause this block to not run and that could be missed. | |
| IF EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = OBJECT_NAME(@@PROCID) | |
| AND EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = 'UpdMyExampleTable' AND IsEnabled = 1) | |
| BEGIN –Logic to only run if trigger is "enabled" | |
| INSERT dbo.MyAuditTable ( TableName, TableID, OldValue ) | |
| SELECT 'MyExampleTable' AS TableName, | |
| MyExampleTableID AS TableID, | |
| MyExampleTableName AS OldValue | |
| FROM Deleted | |
| END | |
| END –Trigger | |
| GO | |
| IF NOT EXISTS (SELECT * FROM dbo.TriggerStatus AS ts WHERE ts.TriggerName = 'UpdMyExampleTable') | |
| INSERT dbo.TriggerStatus ( TriggerName, IsEnabled ) | |
| VALUES ( 'UpdMyExampleTable', 1 ) | |
| GO |
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.