At work we have an old product. It has a bug that affected a small piece of functionality and very few customers were using it, so we disabled it (simple AllowFeature=0 update to the database). However, because we haven’t updated the code in some time, we never removed the functionality that allowed some of the more privileged end users to switch this back on. Fast forward to now and we have a handful of users that have re-enabled this and, sure enough, at least one has complained that it doesn’t work properly.
So we were faced with a choice: spend time rewriting the app to remove this functionality completely; or put something in place that prevents users from re-enabling this. The first option wasn’t realistic for a number of reasons, so we chose the second. The easiest way to achieve this is to add a trigger to reset the value.
It’s been a fair few years since I last used triggers, and that was on Oracle, so I did a short bit of Googling and ended up with something resembling this:
CREATE TRIGGER ForceFeatureDisabling ON ClientSettings AFTER UPDATE AS BEGIN UPDATE ClientSettings Set AllowFeature = 0 FROM ClientSettings INNER JOIN Inserted ON ClientSettings.Id = Inserted.Id END
As you can probably work out, you get into a recursive loop. The original update fires the trigger, which runs an update, which fires the trigger, and so on.
If you’re wondering why I chose to use “AFTER UPDATE” rather than “INSTEAD OF UPDATE”, it’s because there are a couple of dozen columns and writing an update for all of the columns when it was only 1 that I wanted to fix seemed like more effort than I wanted to spend on doing this (did I mention the application hasn’t been properly updated in years?).
Fortunately for me, Microsoft anticipated people doing something like this and so when I ran an update for the first time I was presented with an error message:
Maximum stored procedure, function trigger, or view nesting level exceeded (limit 32)
Fortunately Microsoft, having anticipated this problem, also created a way to resolve this. A function called TRIGGER_NESTLEVEL.
CREATE TRIGGER ForceFeatureDisabling ON ClientSettings AFTER UPDATE AS IF TRIGGER_NESTLEVEL() <= 1 BEGIN SET NOCOUNT ON; UPDATE ClientSettings Set AllowFeature = 0 FROM ClientSettings INNER JOIN Inserted ON ClientSettings.Id = Inserted.Id END
Although this works well enough, as mentioned earlier, the use of an “INSTEAD OF UPDATE” trigger would be better in some ways. It would require a more sizeable SQL query due to the number of columns involved, but it would mean that the table only gets updated once per update request, rather than twice as it currently is.