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.

https://gist.github.com/paschott/3ce4ec6f2205e3bcf372e77310bfd2c4