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".