T-SQL trigger to update columns

Question

I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one is changed to update the other one also like: - if added a ~ in front of the ShortName to set IsInvisible to 1 and if ~ is removed to set it to 0 - if IsInvisible is set to 1 to add ~ in front of the ShortName and when set to 0 to remove it.

I've tried something like:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN

UPDATE t
SET IsInvisible = (CASE WHEN i.ShortName like '~%' THEN 1 ELSE 0 END),
    ShortName = (CASE WHEN i.IsInvisible = 1 AND t.ShortName NOT LIKE '~%'
                 THEN '~' + t.ShortName
                 ELSE t.ShortName
            END)
FROM table t JOIN
     inserted i
     ON t.Id = i.Id;

end

My problem is that when I'm updating one of the columns or both, nothing happens and I get this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

What am I doing wrong?

The "Allow Triggers to Fire Others" option displays "True".

Answer 1

Here is the problem:

1. You update the table
2. It triggers the update trigger: updateInvisibility
3. In the trigger you update the table again so it triggers the updateInvisibility trigger

and then it keeps doing that so you end up triggering the trigger many times in a sort of recursive fashion without even realizing. SQL server will allow this call nesting to be 32 levels deep and then it throws this error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

which is pretty clear.

T-SQL Solution

The code-only solution is to check the nesting level and leave if bigger than 1 (1 is the first time it is called by the update):

IF TRIGGER_NESTLEVEL() > 1 RETURN;

Like this:

ALTER TRIGGER [dbo].[updateInvisibility]
ON [dbo].[table]
AFTER UPDATE
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1 RETURN;

    -- Do your work...
END

Alternative

As pointed out in a comment by RBarryYoung, you can prevent this problem in the first place by disabling recursive triggers.