SQL update trigger loop

The problem

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.

First attempt

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?).

The error

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)

The fix

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

Improvements

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.

Leave a Reply

Your email address will not be published. Required fields are marked *